dynamically create trigger to audit row changes

Question

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

Answer 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.