Disabling triggers to support data generation on DB Dude

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