2

I'm using this approach to create a trigger to catch data changes. However, the system allows schema changes, and I would like to create this trigger dynamically after a new table has been created. I was trying to separate the logic into a stored procedure and call it in the trigger body, but in the stored procedure context, there's no access to inserted / updated. Is there any better approach to achieve the same result?

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

AND FOR EVERY SCHEMA CHANGE I WOULD RUN THIS STATEMENT

PS: trigtest is the new table, I've hardcoded just to test it

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

    exec X @TableNameValue
1

SQL Server supports DDL triggers. This would fire when a table is created, for example.

Constructing the table trigger code dynamically in the DDL trigger would, I believe, achieve the desired outcome.

  • You mean, use DDL Schema Change trigger to create this trigger dinamically? – Thiago Custodio Feb 11 '17 at 23:38
  • Yes; I've reworded. – Michael Green Feb 11 '17 at 23:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.