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.
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.
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
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.
ALTER TRIGGER Trigger2 ON Table2 FOR INSERT AS BEGIN IF TRIGGER_NESTLEVEL() > 2 RETURN INSERT INTO Table1 VALUES (2, 'ABC') END
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.
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
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.
CREATE TRIGGER SchemaChangesAudit ON DATABASE FOR ALTER_TABLE AS INSERT INTO [dbo].[TableChangesAudit] values (GETDATE(), SUSER_NAME())
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
Kate blogs about the details that make the Data Warehouses work
We run regular business intelligence courses in both Wellington and Auckland.