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.

What database are you using? (As the information hovering the sql tag says to add) — Paul T., Dec 23 '21 at 13:10
No worries. I'm not sure about MSSQL, but how is `TRUNCATE TABLE` handled? For MySQL/MariaDB, the trigger is not lost with truncate, it was still present, unlike when doing a `DROP TABLE` statement which also removes any related triggers. There may be subtle differences, such as if the table has foreign keys (and other factors), the truncate may not be a possible alternative. If not, then a solution may be to have a script to drop table, create table, create triggers with a script (or scripts to cover various table sets) depending upon the need. — Paul T., Dec 27 '21 at 03:47
@PaulT. Well, TRUNCATE TABLE requires elevated permissions (as opposed to Delete From table). However, a truncate only removes data (albeit without logging) compared to a DROP/CREATE in which the table, and associated objects, is dropped and then created with alterations. However, the Drop/Create does not seem to create any objects associated with the table (such as a trigger). I think you're right. Something more elaborate is going to be required. That said, I've now taken to scripting the Trigger as CREATE and then saving that after every alteration. Cumbersome but it works. — Bronco638, Dec 28 '21 at 14:29

score 0 · Answer 1 · answered Sep 26 '22 at 19:03

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

Recreate an existing Trigger on Table Drop/Create?

1 Answers1