Recreate an existing Trigger on Table Drop/Create?

Question

I have an insurance claims table (TableX for this example) as well as an Audit table (TableXAudit). The trigger associated with TableX inserts records on Insert, Update and Delete (as you would expect an audit trigger to do). However, TableX is still being modified (adding/removing columns) as the project is being fleshed out. I discovered, the hard way, that using the Drop/Create Table script, to make table changes, results in the Trigger being dropped but not recreated.

My question; Is there a way to preserve the trigger on table Drop/Create?

Thanks.

Answer 1

I know this is old, but this seems to work for the common case. It creates a script to drop and recreate the triggers. You can then place your drop table code in the midst of it.

CREATE TABLE #commands_to_build (
    ID INT IDENTITY(1,1),
    command NVARCHAR(max) 
    )

DECLARE @schema_name sysname = 'your_schema_name_here'
DECLARE @table_name_to_change sysname = 'your_table_name_here'
DECLARE @row_num int

SELECT  
    s.name AS [schema_name],
    o.Name AS TriggerName,
    c.text,
    t.name AS table_name
    INTO #triggercode  
FROM 
    dbo.SysObjects o
    INNER JOIN dbo.sysComments c
    ON o.ID = c.ID
    inner join sys.tables t
    ON t.object_id = o.parent_obj
    INNER JOIN sys.schemas s
    ON s.schema_id = t.schema_id
WHERE   
    o.Type = 'TR'
    AND t.name = @table_name_to_change
    AND s.name = @schema_name
ORDER BY s.name, t.name, o.name 

INSERT INTO #commands_to_build (command)
SELECT X.command
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY TriggerName) * 2 AS row_num, 
    'DISABLE TRIGGER [' + TriggerName + '] ON [' + @schema_name + '].[' + @table_name_to_change + ']' AS command
    FROM #triggercode
    UNION 
    SELECT ROW_NUMBER() OVER (ORDER BY TriggerName) * 2 -1, 'GO'
    FROM #triggercode
) X
ORDER BY X.row_num

Select @row_num = Count(*) From #triggercode
  
IF @row_num > 0
BEGIN

/* This rigamorole inserts a GO command between every create and enable trigger command, 
in a grouping of four commands, GO, CREATE, GO, ENABLE since they each must be the first of a batch */
    INSERT INTO #commands_to_build (command)
    SELECT X.command
    FROM
    (
        SELECT ROW_NUMBER() OVER (ORDER BY TriggerName) * 4 - 3 AS row_num,
        'GO' AS command
        FROM #triggercode
        UNION 
        SELECT ROW_NUMBER() OVER (ORDER BY TriggerName) * 4 - 2, 
        [text]
        FROM #triggercode
        UNION
        SELECT ROW_NUMBER() OVER (ORDER BY TriggerName) * 4 - 1,
        'GO'
        FROM #triggercode
        UNION
        SELECT ROW_NUMBER() OVER (ORDER BY TriggerName) * 4 AS row_num, 
        'ENABLE TRIGGER [' + TriggerName + '] ON [' + @schema_name + '].[' + @table_name_to_change + ']' AS command
        FROM #triggercode
    ) X
    ORDER BY X.row_num
end

SELECT * FROM #commands_to_build