動態創建觸發器以審核行更改

我正在使用這種方法來創建一個觸發器來擷取數據更改。但是，系統允許模式更改，我想在創建新表後動態創建此觸發器。我試圖將邏輯分離到一個儲存過程中並在觸發器主體中呼叫它，但是在儲存過程上下文中，無法訪問插入/更新。有沒有更好的方法來達到相同的結果？

CREATE PROC X
   @TableNameValue varchar(50)
AS

   declare @bit int ,
       @field int ,
       @maxfield int ,
       @char int ,
       @fieldname varchar(128) ,
       @TableName varchar(128) ,
       @PKCols varchar(1000) ,
       @sql varchar(2000), 
       @UpdateDate varchar(21) ,
       @UserName varchar(128) ,
       @Type char(1) ,
       @PKFieldSelect varchar(1000),
       @PKValueSelect varchar(1000)

       select @TableName = @TableNameValue
       -- date and user
       select @UserName = system_user ,
       @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
       -- Action
       if exists (select * from inserted)
       if exists (select * from deleted)
       select @Type = 'U'
           else
       select @Type = 'I'
           else
       select @Type = 'D'

       -- get list of columns
       select * into #ins from inserted
       select * into #del from deleted
       -- Get primary key columns for full outer join
       select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
       from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       where pk.TABLE_NAME = @TableName
       and CONSTRAINT_TYPE = 'PRIMARY KEY'
       and c.TABLE_NAME = pk.TABLE_NAME
       and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
       -- Get primary key fields select for insert
       select @PKFieldSelect = coalesce(@PKFieldSelect+'+','') + '''' + COLUMN_NAME + '''' 
       from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       where pk.TABLE_NAME = @TableName
       and CONSTRAINT_TYPE = 'PRIMARY KEY'
       and c.TABLE_NAME = pk.TABLE_NAME
       and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
       select @PKValueSelect = coalesce(@PKValueSelect+'+','') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))'
       from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE c   
       where  pk.TABLE_NAME = @TableName   
       and CONSTRAINT_TYPE = 'PRIMARY KEY'   
       and c.TABLE_NAME = pk.TABLE_NAME   
       and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 

       if @PKCols is null
       begin
           raiserror('no PK on table %s', 16, -1, @TableName)
           return
       end

並且對於每次架構更改，我都會執行此語句

PS： trigtest 是新表，我已經硬編碼只是為了測試它

create trigger trigtest_ChangeTracking on trigtest for insert, update, delete
as
   declare @TableNameValue varchar(50)
   set @TableNameValue = 'trigtest'

   exec X @TableNameValue

SQL Server 支持DDL 觸發器。例如，這將在創建表時觸發。

我相信，在 DDL 觸發器中動態建構表觸發器程式碼會達到預期的效果。

引用自：https://dba.stackexchange.com/questions/164057