Introduction
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, we’ll 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
GOCREATE TABLE Test
(
col1 varchar(50)
);
Next we’ll 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
ASINSERT 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. We’ll 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, we’ll 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
ASINSERT 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, we’ll monitor
all inserts of the Person.Address table, and if an address contains the
abbreviation “Ave”, we’ll 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 we’ve 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
ASBEGIN
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 doesn’t
exist, we’ll just insert what was entered.
Next we’ll insert a record
containing the “Ave” abbreviation.
INSERT INTO Person.Address
(AddressLine1, City, StateProvinceID, PostalCode)
VALUES
(‘address3 Ave’, ‘city3’, 79, ‘33333’);
The new trigger didn’t utilize the
Test table we created, so instead we’ll select directly from the Person.Address
table to verify the trigger worked successfully.
SELECT AddressLine1
FROM Person.Address
WHERE PostalCode = ‘33333’;
Conclusion
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.