Azure Synapse是否支持DML触发器？替代方案及表删除监控咨询

一、Synapse SQL池是否支持DML触发器？ #

Synapse SQL池（包括专用SQL池和无服务器SQL池）不支持传统的DML触发器（即由Insert/Update/Delete操作直接触发的数据库级触发器），这是和Oracle ADW的核心差异之一。

二、监控Delete操作的替代方案 #

针对从Oracle ADW迁移后的Delete操作监控需求，可采用以下几种落地方法：

1. 审计日志（Audit Logs） #

• 启用Synapse的工作区审计日志，配置记录DELETE相关的SQL操作。审计日志会捕获执行Delete语句的用户、时间、语句内容等信息，可通过Log Analytics分析或导出到存储账户持久化。
• 操作要点：在Synapse工作区的“监控”>“设置”>“审计”中开启，指定日志目标（Log Analytics/存储账户/Event Hub），然后通过Kusto查询筛选Delete事件：

  AzureDiagnostics
  | where Category == "SqlPoolAuditLogs"
  | where action_name_s == "DELETE"
  | project time_generated, user_name_s, sql_text_s, database_name_s
  

2. 自定义操作日志表 #

• 在业务流程中强制通过存储过程执行Delete操作，在存储过程中添加日志写入逻辑：

  CREATE PROCEDURE dbo.Delete_YourTable
  @Id INT
  AS
  BEGIN
      -- 写入删除日志
      INSERT INTO dbo.Table_Delete_Logs (DeleteTime, DeletedBy, DeletedId)
      VALUES (GETDATE(), SUSER_SNAME(), @Id);
  
      -- 执行实际删除
      DELETE FROM dbo.YourTable WHERE Id = @Id;
  END
  

• 要求所有Delete操作必须调用该存储过程，通过权限管控（如撤销普通用户的Delete权限，仅存储过程拥有Delete权限）确保合规。

3. 变更数据捕获（CDC） #

• 启用Synapse专用SQL池的**变更数据捕获（CDC）**功能，CDC会自动捕获表的Insert/Update/Delete变更，包括删除操作的旧数据快照。
• 操作要点：
  1. 启用数据库级CDC：EXEC sys.sp_cdc_enable_db;
  2. 启用目标表的CDC（指定捕获Delete操作）：

     EXEC sys.sp_cdc_enable_table
     @source_schema = N'dbo',
     @source_name = N'YourTable',
     @role_name = NULL,
     @supports_net_changes = 1;
     

  3. 查询CDC捕获的删除数据：

     SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_YourTable(@start_lsn, @end_lsn, N'all');
     -- 筛选删除操作
     SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_YourTable(@start_lsn, @end_lsn, N'all') WHERE __$operation = 3;
     

  • 注意：CDC仅支持专用SQL池，无服务器SQL池暂不支持。

4. 逻辑删除+版本表（针对重要业务表） #

• 为目标表添加DeletedFlag（BIT类型）和DeletedTimestamp（DATETIME类型）字段，将物理删除改为逻辑删除：

  ALTER TABLE dbo.YourTable ADD DeletedFlag BIT DEFAULT 0, DeletedTimestamp DATETIME NULL;
  
  -- 执行逻辑删除
  UPDATE dbo.YourTable SET DeletedFlag = 1, DeletedTimestamp = GETDATE() WHERE Id = @Id;
  

• 后续通过查询DeletedFlag=1的记录监控删除操作，定期归档逻辑删除的数据。这种方案适合需要保留删除数据并审计的场景。

内容的提问来源于stack exchange，提问作者KKU