Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Disabling Triggers
CREATE TABLE TriggerStatus (
TriggerStatusID INT IDENTITY(1,1) NOT NULL,
TriggerName varchar(100) NOT NULL,
IsEnabled BIT NOT NULL CONSTRAINT DF_TriggerStatus_IsEnabled DEFAULT 1,
CONSTRAINT PK_TriggerStatus PRIMARY KEY CLUSTERED (TriggerStatusID)
)
GO
CREATE TABLE MyAuditTable (
MyAuditTableID INT NOT NULL IDENTITY(1,1),
TableName varchar(100) NOT NULL,
TableID INT NOT NULL,
OldValue VARCHAR(100) NOT NULL,
CONSTRAINT PK_MyAuditTable PRIMARY KEY CLUSTERED (MyAuditTableID)
)
GO
CREATE TABLE dbo.MyExampleTable (
MyExampleTableID INT IDENTITY(1,1) NOT NULL,
MyExampleTableName VARCHAR(100) NOT NULL,
CreatedDate DATETIME2 NOT NULL CONSTRAINT DF_MyExampleTable_CreatedDate DEFAULT GETUTCDATE(),
UpdatedDate DATETIME2 NULL,
CONSTRAINT PK_MyExampleTable PRIMARY KEY CLUSTERED (MyExampleTableID)
)
GO
CREATE TRIGGER UpdMyExampleTable ON dbo.MyExampleTable FOR UPDATE
AS
BEGIN --Trigger
UPDATE met SET met.UpdatedDate = GETUTCDATE()
FROM dbo.MyExampleTable AS met
JOIN Inserted AS i
ON i.MyExampleTableID = met.MyExampleTableID
--Check for both the row existing in the TriggerStatus table AND that the trigger is marked as enabled.
--Lack of a row will cause this block to not run and that could be missed.
IF EXISTS (SELECT * FROM dbo.TriggerStatus AS ts WHERE ts.TriggerName = OBJECT_NAME(@@PROCID)
AND EXISTS (SELECT * FROM dbo.TriggerStatus AS ts WHERE ts.TriggerName = 'UpdMyExampleTable' AND IsEnabled = 1)
BEGIN --Logic to only run if trigger is "enabled"
INSERT dbo.MyAuditTable ( TableName, TableID, OldValue )
SELECT 'MyExampleTable' AS TableName,
MyExampleTableID AS TableID,
MyExampleTableName AS OldValue
FROM Deleted
END
END --Trigger
GO
IF EXISTS (SELECT * FROM dbo.TriggerStatus AS ts WHERE ts.TriggerName = 'UpdMyExampleTable')
INSERT dbo.TriggerStatus ( TriggerName, IsEnabled )
VALUES ( 'UpdMyExampleTable', 1 )
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment