DML Triggers in SQL Server 2008
September 4, 2009
Triggers are stored TSQL code that is used to automatically perform a task when some type of SQL Server action occurs. As their name implies, a Trigger is triggered or set off by some monitored action. These monitored actions can be one of three categories: DML, DDL, or a Logon Event. A DML (Data Manipulation Language) trigger fires when an Insert, Update, or Delete statement is performed. DDL Triggers (Data Definition Language) are activated by a schema level event such as creating a table or altering an index. The last trigger type, Logon Trigger, is executed when a user begins a SQL Server session. This article will focus on DML triggers.
An interesting feature of triggers is their ability to fire either after some event occurs, or instead of. Meaning not only can a trigger be used to monitor events after they happen, but can be used to redirect or alter events. For example, we could create a trigger that would ignore any DELETE statement that would remove more than an a thousand rows.
DML After Trigger
In this first example, well create a trigger that will write a row to an auditing table anytime an insert is performed on the AdventureWorks.Person.Address table. To begin, create a test table that the trigger will write to with the following TSQL code:
USE AdventureWorks GO CREATE TABLE Test ( col1 varchar(50) );
Next well create the trigger code that will fire anytime an insert is performed on Person.Address table. After the INSERT happens, the trigger should write a row to our test auditing table perviously created.
CREATE TRIGGER TestTrigger1 ON Person.Address AFTER INSERT AS INSERT INTO Test (col1) VALUES ('trigger fired');
The basic syntax of a Trigger is similar in structure to other TSQL statements such as stored procedures. The first line specifies that we are creating a trigger with the name TestTrigger1. The normal TSQL rules for naming objects applies also to triggers.
The next line ON Person.Address specifies the table the trigger is assigned to. DML Triggers must be attached to a table or view. So to monitor any insert on every table in your database would require a trigger on each table (if our only goal was auditing, SQL Server 2008 has better options than using a trigger).
Now we specify what type of DML activity to watch for. Our choices are AFTER INSERT, AFTER UPDATE, AFTER DELETE, INSTEAD OF INSERT, INSTEAD OF UPDATE, or INSTEAD OF DELETE. Note that the keyword FOR can be used interchanably with AFTER.
Everything after the AS keyword is the task the trigger should perform when fired. Well simply insert the text trigger fired into our Test table.
To test our trigger, insert a row into the Person.Address table using the following TSQL:
INSERT INTO Person.Address (AddressLine1, City, StateProvinceID, PostalCode) VALUES ('address1', 'city1', 79, '53150');
Notice two rows affected are displayed in the messages area, one for the insert and the other for the trigger.
Selecting our Test table will show that the trigger successfully fired as shown below.
The trigger object can be viewed from the SQL Server Management Studio by expanding Tables, Person.Address, and then selecting Triggers.
Inserted and Deleted Tables
Triggers have access to two special tables that track deleted items and inserted items. The Inserted and Deleted tables are automatically managed by SQL Server. In this second example, well capture the zip code inserted into the Person.Address table and copy it to our Test auditing table. Modify the trigger previously created to select the PostalCode from the Person.Address table as shown.
ALTER TRIGGER Person.TestTrigger1 ON Person.Address AFTER INSERT AS INSERT INTO Test (col1) SELECT PostalCode FROM Inserted;
Insert a new record into the Person.Address table with a zip code of 22222 using the following TSQL:
INSERT INTO Person.Address (AddressLine1, City, StateProvinceID, PostalCode) VALUES ('address2', 'city2', 79, '22222');
Now when we select from out Test table, two rows will be returned, the origianl row trigger fired and new row showing the zip code 22222 from the inserted table.
DML Instead Of Trigger
The combination of an Instead Of trigger using the Deleted or Inserted table provides a powerful base to build business logic rule enforcement on. In this next example, well monitor all inserts of the Person.Address table, and if an address contains the abbreviation Ave, well change it to the full word Avenue.
First, remove the old records in the Test database and remove the previously created trigger with the following TSQL code:
TRUNCATE TABLE TEST; DROP TRIGGER Person.TestTrigger1;
This example at first glance appears complex but its components are the same basic entities weve been working with. Again, the purpose of this trigger is to monitor every insert for the abbreviation Ave and replace it with the full word Avenue.
CREATE TRIGGER TestTrigger2 ON Person.Address INSTEAD OF INSERT AS BEGIN IF EXISTS ( SELECT AddressLine1 FROM Inserted WHERE RIGHT(AddressLine1, 3) = 'Ave' ) INSERT INTO Person.Address (AddressLine1, City, StateProvinceID, PostalCode) SELECT REPLACE(AddressLine1 , 'Ave', 'Avenue'), City, StateProvinceID, PostalCode FROM Inserted; ELSE INSERT INTO Person.Address (AddressLine1, City, StateProvinceID, PostalCode) SELECT AddressLine1, City, StateProvinceID, PostalCode FROM Inserted; END;
The first two beginning lines are identical to the previous example where we give the trigger a name and specify what table it should monitor. Next, we use the Instead of Insert key phrase so our trigger will fire prior to the new row being inserted. The If Exists statement looks for the abbreviation Ave at the end the new row to be inserted. If it exists, we replace it with the word Avenue, if it doesnt exist, well just insert what was entered.
Next well insert a record containing the Ave abbreviation.
INSERT INTO Person.Address (AddressLine1, City, StateProvinceID, PostalCode) VALUES ('address3 Ave', 'city3', 79, '33333');
The new trigger didnt utilize the Test table we created, so instead well select directly from the Person.Address table to verify the trigger worked successfully.
SELECT AddressLine1 FROM Person.Address WHERE PostalCode = '33333';
Data Manipulation Triggers can be called after a statement executes After, or prior to Instead Of. The Trigger can monitor Insert, Update, and Delete statements. Triggers also have access to special Inserted and Deleted tables. Triggers are often used to enforce business logic and monitor events.