VWRBVN0N

1CREATE TABLE TriggerStatus (
2TriggerStatusID INT IDENTITY(1,1) NOT NULL,
3TriggerName varchar(100) NOT NULL,
4IsEnabled BIT NOT NULL CONSTRAINT DF_TriggerStatus_IsEnabled DEFAULT 1,
5CONSTRAINT PK_TriggerStatus PRIMARY KEY CLUSTERED (TriggerStatusID)
6)
7GO
8
9CREATE TABLE MyAuditTable (
10MyAuditTableID INT NOT NULL IDENTITY(1,1),
11TableName varchar(100) NOT NULL,
12TableID INT NOT NULL,
13OldValue VARCHAR(100) NOT NULL,
14CONSTRAINT PK_MyAuditTable PRIMARY KEY CLUSTERED (MyAuditTableID)
15)
16GO
17
18CREATE TABLE dbo.MyExampleTable (
19MyExampleTableID INT IDENTITY(1,1) NOT NULL,
20MyExampleTableName VARCHAR(100) NOT NULL,
21CreatedDate DATETIME2 NOT NULL CONSTRAINT DF_MyExampleTable_CreatedDate DEFAULT GETUTCDATE(),
22UpdatedDate DATETIME2 NULL,
23CONSTRAINT PK_MyExampleTable PRIMARY KEY CLUSTERED (MyExampleTableID)
24)
25GO
26
27CREATE TRIGGER UpdMyExampleTable ON dbo.MyExampleTable FOR UPDATE
28AS
29BEGIN --Trigger
30
31	UPDATE met SET met.UpdatedDate = GETUTCDATE()
32	FROM dbo.MyExampleTable AS met
33	JOIN Inserted AS i
34		ON i.MyExampleTableID = met.MyExampleTableID
35
36  --Check for both the row existing in the TriggerStatus table AND that the trigger is marked as enabled.
37  --Lack of a row will cause this block to not run and that could be missed.
38	IF EXISTS (SELECT * FROM dbo.TriggerStatus AS ts WHERE ts.TriggerName = OBJECT_NAME(@@PROCID)
39  AND EXISTS (SELECT * FROM dbo.TriggerStatus AS ts WHERE ts.TriggerName = 'UpdMyExampleTable' AND IsEnabled = 1)
40	BEGIN --Logic to only run if trigger is "enabled"
41		INSERT dbo.MyAuditTable ( TableName, TableID, OldValue )
42		SELECT 'MyExampleTable' AS TableName,
43			MyExampleTableID AS TableID,
44			MyExampleTableName AS OldValue
45		FROM Deleted
46	END
47
48END --Trigger
49GO
50
51IF EXISTS (SELECT * FROM dbo.TriggerStatus AS ts WHERE ts.TriggerName = 'UpdMyExampleTable')
52INSERT dbo.TriggerStatus ( TriggerName, IsEnabled )
53VALUES ( 'UpdMyExampleTable', 1 )
54GO