Prevent missing trigger while database migration

Prevent missing trigger while database migration

    While migrating scripts from one server to another server or from one environment to other environment ( from QA to Staging) the main challenge you will face is if new column is added at the middle of the table not at end. Then while migrating you have to dump the data in any temp table and then drop and recreate the table with adding new column, and bring back the data in this table.

  But here major issue is you will miss the trigger that you have on your table. So lets take one example , I have to migrate the database , in this migration script there are many drop and create table steps are there. I should have to protect the trigger , not to miss.  

    To Prevent trigger simply , before running migration script , I have dump all trigger script in one table and then after run migration script I pull the script from dump table and recreate all.

Below is the script that I used to pull all trigger script.

--Before executing migration script

SELECT object_name(t.object_id) AS TriggerNAme

,OBJECT_NAME(parent_id) parentObjectNAme

,sm.DEFINITION AS script

INTO mytriggertable

FROM sys.triggers t

INNER JOIN sys.sql_modules sm ON sm.object_id = t.object_id

WHERE object_name(t.object_id) IS NOT NULL

GO

--After migration script

SELECT script + CHAR(13) + 'GO'

FROM mytriggertable

WHERE triggername NOT IN (

SELECT object_name(t.object_id) AS TriggerNAme

FROM sys.triggers t

INNER JOIN sys.sql_modules sm ON sm.object_id = t.object_id

WHERE object_name(t.object_id) IS NOT NULL

)