Disabling Triggers

Disabling_Triggers.sql

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