When deploying test data to database (such as your sandbox database) with triggers, you need that the triggers will be disabled before generating test data and then re-enabled after the data has been appropriately generated.

I wrote two scripts for this issue: DisableTriggers.sql , EnableTriggers.sql .

Execute this T-Sql before deploying test data:

DisableTriggers.sql

IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘#AllTriggers’) AND type in (N ‘U’))
DROP TABLE #AllTriggers

–Getting all trigger names ad on what tables and schemas they reside:
–Put the list in a temp tab
SELECT T.Name as TrigName, o.Name as TabName, s.Name as SchemaName
INTO #AllTriggers
FROM sys.triggers T join sys.objects o
ON T.parent_Id = o. object_ID
JOIN sys.schemas s
ON o.schema_Id = s.Schema_ID

–Disabling all triggers: A cursor to run over the temp table
DECLARE TrigCurs Cursor
FOR SELECT TrigName, TabName, SchemaName FROM #AllTriggers

OPEN TrigCurs

DECLARE @TrigName varchar(250), @TabName varchar(250), @SchameName VarChar(250), @cmd varchar(1000)

FETCH Next FROM TrigCurs INTO @TrigName , @TabName , @SchameName

WHILE @@Fetch_Status = 0
BEGIN
SET @cmd = ‘disable trigger all on ‘+ @SchameName+ ‘.’+@TabName+‘;’
EXEC (@cmd)

FETCH Next FROM TrigCurs INTO @TrigName , @TabName , @SchameName
END
GO

I execute it from the Script.PreDeployment.sql  file.

Re-enable them after the data was generated. The T-Sql is:

EnableTriggers.sql

–Enabling back all triggers: A cursor to run over the temp tab
DECLARE TrigCurs2 Cursor
FOR SELECT TrigName, TabName, SchemaName from #AllTriggers

OPEN TrigCurs2

DECLARE @TrigName varchar(250), @TabName varchar(250), @SchameName VarChar(250), @cmd varchar(1000)

FETCH Next from TrigCurs2 into @TrigName , @TabName , @SchameName

WHILE @@Fetch_Status = 0
BEGIN
SET @cmd = ‘enable trigger all on ‘+ @SchameName+ ‘.’+@TabName+‘;’
EXEC (@cmd)

FETCH Next FROM TrigCurs2 INTO @TrigName , @TabName , @SchameName
END

DROP TABLE #AllTriggers
GO

I execute it from the Script.PostDeployment.sql  file.

You can download both scripts: EnableTriggers.sql , DisableTriggers.sql

Similar Posts:

• How to prevent SQL injections
• Search File System Using LINQ
• Save datagrid changes in the database
• SqlMetal for Linq
• Getting started with Astoria

Tags: VSTS DB Pro