Tracking with Triggers

Have you ever come across triggers being used to audit table changes? In this post, we’ll look at a few examples of DML triggers in action.

Tracking LastEditDate with Trigger

First, let’s look at tracking the timing of record updates for a table by including a LastEditDate column.

Let’s create a table for comic books and insert a few popular comic book titles:

CREATE TABLE Comic
(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	Title VARCHAR(100) NOT NULL,
	LastEditDate DATETIME2 DEFAULT SYSUTCDATETIME()
);
GO

INSERT INTO Comic (Title)
VALUES ('Daredevil'),('Spider-Man'),('Iron Man');
GO

We only inserted titles since we have LastEditDate with a default configured. We’ll run a SELECT statement to verify we have our titles and a LastEditDate value as the time the titles were entered.

SELECT * FROM Comic;
GO

Now, let’s create our trigger. Each time a record gets updated, we want to keep track of when that update occurred by updating the time in LastEditDate. We’ll create a trigger to update the LastEditDate based on the Id of the record being updated:

CREATE TRIGGER TR_Comic_LastEditDate ON Comic
AFTER UPDATE
AS
UPDATE Comic
SET LastEditDate = SYSUTCDATETIME()
WHERE Id IN (
		SELECT Id
		FROM inserted
		);
GO

Spider-Man is a great title but I think there’s a little something missing. Let’s update that title to “The Amazing Spider-Man” by running the following:

UPDATE Comic
SET Title = 'The Amazing Spider-Man'
WHERE Id = 2;
GO

Note the two separate lines stating “1 row affected” when our UPDATE ran. One line is for our UPDATE and one line is the trigger executing after the UPDATE.

If we check our Comic table, we’ll see the title change from our UPDATE statement as well as an updated LastEditDate value tracking when that title change was made:

SELECT * FROM Comic;
GO

Logging to Table with Trigger

The previous example let’s us know the last time a record was updated but what if we wanted to log when a record was deleted?

We can create a table to log deletions:

CREATE TABLE TitleLog
(
	TL_Id INT IDENTITY(1,1) PRIMARY KEY,
	TL_Event VARCHAR(10),
	TL_Title VARCHAR(100),
	TL_User VARCHAR(100),
	TL_Date DATETIME2
);
GO

And add a new trigger that will insert a record to TitleLog each time a title is deleted:

CREATE TRIGGER TR_Comic_Del
ON Comic
AFTER DELETE
AS

INSERT TitleLog
SELECT
	'DELETE',			
	Title,
	SUSER_SNAME(),
	SYSUTCDATETIME()
FROM deleted;
GO

We updated our “The Amazing Spider-Man” record earlier but now we’ve decided to get rid of it entirely. If we delete the record:

DELETE FROM Comic
WHERE Id = 2;
GO

The TR_Comic_Del trigger will fire and we can see a new row added to TitleLog:

SELECT * FROM TitleLog;
GO

What to Watch For

If you have a heavy workload and triggers are constantly firing then performance can take a hit. We noticed above that executing one statement will cause more follow up from triggers. Keep that in mind.

In the case of logging user info, make sure your users are unique. If multiple people are logging in with a generic user account (or, heaven forbid, sa) then you’re going to have trouble identifying one person making changes.

Thanks for reading!