Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 4, 2009

DML Triggers in SQL Server 2008

By Don Schlichting

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
GO

CREATE 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
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. 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.

Notice two rows affected are displayed in the messages area

Selecting our Test table will show that the trigger successfully fired as shown below.

the trigger successfully fired

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
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, 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
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 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';

select directly from the Person.Address table to verify the trigger worked

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.

» See All Articles by Columnist Don Schlichting



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM