Triggers in Microsoft SQL Server

Triggers are pieces of code which are automatically executed on the occurrence of an event in the database. They can be a good solution for a whole range of problems. However, many SQL Server users choose to avoid using triggers as they are well-hidden from sight and can cause issues. If users are not aware of the trigger’s existence in the database, they could find that data update doesn’t go as they have planned.
I would like to share with you some examples from the projects I have been working on where I used triggers.

RyanMcGuire / Pixabay

Data of Future Past

Logical tables INSERTED and DELETED are very useful when it is important to trace the change to the data. During the event ‘data update’, new data rows are available in the table INSERTED; and the data rows which will be replaced are available in the table DELETED. This approach has been used for implementing the data warehouse audit on one of the projects I’ve worked on.
Let’s say there is a database My_DB for the current state of the data and an audit database DB_Audit to store all the previous data states. For each of the tables in My_DB I create a table in DB_Audit. Also, I create a trigger that saves the old state of the data when it’s about to be replaced and a timestamp of change.
[sql]USE My_DB;
CREATE TABLE [My_Table](
TableKey int IDENTITY(1,1)
, NameField varchar(100)
, AddressField varchar(150)
, DateOfBirth date
);
USE DB_Audit;
CREATE TABLE [My_Table_Audit](
TableKey int
, NameField varchar(100)
, AddressField varchar(150)
, DateOfBirth date
, AuditTimeStamp datetime
);
USE My_DB;
CREATE TRIGGER My_Table_Audit
ON My_Table
FOR UPDATE, DELETE
AS
INSERT INTO DB_Audit.dbo.My_Table_Audit
SELECT *, GETDATE() FROM DELETED
[/sql]

An Almost Never Ending Story

Let’s say Table1 has a trigger which inserts a record into Table2 on every insert into Table1; and Table2 has a trigger that inserts a record into Table1 on every insert into Table2. Once someone inserts a value into Table1, it fires an infinite loop of inserts via triggers until the world ends… Not exactly. Although SQL Server doesn’t check a trigger’s code for mutual dependencies like the one described above, it doesn’t allow more than 32 levels of trigger nesting. This “never ending” process will, therefore, fail after it reaches the limit of nest levels, insert transaction will be cancelled and nothing will be inserted at all. To prevent this, you can add a check for a current trigger’s nesting level in your trigger’s code and implement a suitable behaviour instead of letting SQL Server fail the whole insert.
[sql]ALTER TRIGGER Trigger2
   ON Table2
FOR INSERT
AS
BEGIN
  IF TRIGGER_NESTLEVEL() > 2 RETURN
  INSERT INTO Table1
 VALUES (2, ‘ABC’)
END
[/sql]

A Point of View

From the database users’ perspective, views are no different from tables even if we all know that views are tables which don’t physically exist. In SQL Server you can execute INSERT, UPDATE and DELETE statements against views. And in fact, views could have triggers executed on the occurrence these events the same as tables. On one of my projects, I have used a view as an interface to surface data from multiple tables and pass the INSERT and UPDATE statements into the actual tables via triggers.
[sql]CREATE VIEW vwCustomer
AS
SELECT
Customer.ID
, Customer.Name
, Addresses.City
FROM Customer
JOIN Addresses ON Customer.ID = Addresses.CustomerID;
CREATE TRIGGER Customer_Update
ON vwCustomer
INSTEAD OF UPDATE
AS
BEGIN
MERGE INTO Customer
USING (SELECT [ID], [Name] FROM INSERTED) AS Source
ON Customer.ID = Source.ID
WHEN MATCHED THEN
UPDATE SET [Name] = Source.[Name];
MERGE INTO Addresses
USING (SELECT [ID], [City] FROM INSERTED) AS Source
ON Addresses.CustomerID = Source.ID
WHEN MATCHED THEN
UPDATE SET [City] = Source.[City];
END
[/sql]

Who Let the Dogs Out?

Triggers could be invoked by data definition language (DDL) events as well. These events happen on schema changes, for example upon adding or dropping columns. On one of my projects, we used DDL triggers to audit changes to the database schema. Although every change was documented well, a table of schema events generated by this trigger helped a lot with finding the right point in the documentation for the date of change.
[sql]CREATE TRIGGER SchemaChangesAudit
ON DATABASE
FOR ALTER_TABLE
AS
INSERT INTO [dbo].[TableChangesAudit] values (GETDATE(), SUSER_NAME())
[/sql]Function EVENTDATA() can be used in this type of trigger. It returns an XML value with all the details of the event, for example, the table name, column name and the command executed. This document on the Microsoft website describes how to use this function. It also points to the XSD schema on your PC which helps with the XML output parsing. I found mine in a different folder though.
I believe there are more nice ways to use triggers, and a whole range of nice functionality is available for use in a trigger’s code. Use them wisely!
Masseuse of all the Data – Kate