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

)

Tracking changes of a table using trigger

Audit Table data for new values old values

Requirement: I have a table and the data of that table is getting change very frequently. I need to trace the all change of that table so I can know at any point of time what values got changed and what was the values before also some time you need to know after deletion of data what was the data I have deleted .

Below is my employee table

Id	Name	Address1	Adress2	Sal
1	Amit	Dehradun	Gaya	12000
2	Praveen	Gaya	Delhi	19000
3	Sachine	Gaya	Banglore	20000

    

AuditTable

Id	Column name	Old Values	New Values	AuditDate
1	Name	Amit	Amit kumar	12 jan 2014
1	Address2	Gaya	Aurangabad	17 jan 2014

Solution: I have created trigger on all Insert, Update, and Delete event on employee table.

  USE [exp]

GO

/****** Object:  Table [dbo].[emp]    Script Date: 3/12/2014 7:53:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[employee](

[id] [int] NULL,

[name] [varchar](100) NULL,

[address] [varchar](100) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

USE [exp]

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (1, N'Amit', N'Gaya himachal')

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (2, N'Suresh', N'panjab')

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (3, N'Sachine', N'Banglore')

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (4, N'Bubly', N'Delhi')

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (5, N'Praveen ', N'Gaya')

GO

USE exp

go

alter TRIGGER dbo.tgr_employee 

ON  [dbo].[employee]

AFTER update

AS 

BEGIN

select a.id,a.ColumnName,a.VALUE as newValues,b.VALUE as OldValues from (

SELECT id,ColumnName,value FROM 

  (SELECT ID, NAME,address FROM INSERTED) p

UNPIVOT

  (VALUE FOR ColumnName IN  (NAME,address))AS unpvt

) a 

INNER JOIN 

(

SELECT id,ColumnName,value FROM 

   (SELECT ID, NAME,ADDRESS FROM deleted) p

UNPIVOT

   (VALUE FOR ColumnName IN  (NAME,ADDRESS) 

)AS unpvt

)b on a.id=b.id and a.ColumnName=b.ColumnName 

 and  a.VALUE<>b.VALUE

END 

GO

SELECT * FROM DBO.employee

UPDATE DBO.employee SET name='Sumit' WHERE ID=5                       

Here you will get result set , you just have to write insert statement in the trigger as per requirement to load data in your audit table.