Last active
July 12, 2023 15:52
-
-
Save paschott/3ce4ec6f2205e3bcf372e77310bfd2c4 to your computer and use it in GitHub Desktop.
Disabling Triggers
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 NOT 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