The title of this post is a tribute to the fantastic Conor vs. SQL blog.

I was working on a database with several triggers on each table. The triggers audited INSERTs, UPDATEs and DELETEs by inserting records to shadow tables in a separate logging database. The database was backed up from one environment and restored to another in which the name of separate logging database was different. I wrote the following script to automatically update all the triggers, replacing any occurrence of the old logging database name with the correct new name.

Heads up: you could really do some damage with this script. Backup and test a restore from that backup before using it!

DECLARE @replaceThis VARCHAR(8000)
DECLARE @withThis VARCHAR(8000)
SET @replaceThis = 'OldLogDatabaseName'
SET @withThis = 'NewLogDatabaseName'
DECLARE c Cursor
FOR
    SELECT
        object_id,
        SCHEMA_NAME(schema_id) AS schema_name,
        name AS object_name
    FROM sys.objects
    where type_desc = 'SQL_TRIGGER'
    ORDER BY modify_date;
OPEN c
DECLARE @triggerObjectId AS INT
DECLARE @triggerSchema AS NVARCHAR(4000)
DECLARE @triggerName AS NVARCHAR(4000)
DECLARE @triggerDefinition AS NVARCHAR(MAX)
DECLARE @sql AS NVARCHAR(MAX)
FETCH NEXT FROM c INTO @triggerObjectId, @triggerSchema, @triggerName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    SELECT    @triggerDefinition = m.definition
    FROM    sys.sql_modules m, sys.triggers t
    WHERE    m.object_id = t.object_id
        AND t.object_id = @triggerObjectId
    PRINT 'Dropping ' + @triggerName + '...'
    SET @sql = 'DROP TRIGGER ' + @triggerSchema + '.' + @triggerName
    EXEC sp_ExecuteSql @sql;
    PRINT 'Recreating ' + @triggerName + '...'
    SET @sql = REPLACE(@triggerDefinition, @replaceThis, @withThis)   
    EXEC sp_ExecuteSql @sql;
    FETCH NEXT FROM c INTO @triggerObjectId, @triggerSchema, @triggerName
END
CLOSE c
DEALLOCATE c