n this section I will provide an approach to coding and debugging triggers and discuss the new features that have been added in SQL Server version 7 regarding triggers.

Why are triggers hard to code?
When coding a typical SQL statement, for example a SELECT, it is easy to test the query. One just has to run it in ISQL. The tables involved in the join are present and can be analyzed. This is not the case with triggers. Even to execute the code, one has to fire off the event that the trigger handles. In addition, the triggers make heavy use of the inserted and deleted tables (see part 2 for a description of these tables) which are only visible from within the trigger.

How to code a trigger?
The approach I suggest has three steps. In the first step, we will create a trigger that does nothing other than create a permanent copy of the inserted and deleted tables.

The second step consists of coding the action we want the trigger to implement using the copies of the inserted and deleted tables.

The third step consists of taking the code that we have created and tested in the second step and placing it into the trigger. I will illustrate my approach with an example.

Following is the definition of an employee table and the audit table that should record relevant changes to the employee status. (The following example will work in both versions 6.5 and 7 except for the reference to INFORMATION_SCHEMA, which is specific to version 7.)

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'employee' )	drop table employeegocreate table employee(	empid int identity(1,1) constraint pkemployee primary key (empid),	deptid int,	name varchar(30),	salary float,	bonus float,	grade int)goif exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'au_salarychange')	drop table au_salarychangegocreate table au_salarychange(changeid int identity(1,1) constraint pkau_salarychange primary key(changeid),	empid int,	datechanged datetime,	who varchar(30),	prevsalary float,	newsalary float,	prevgrade int,	newgrade int,	prevbonus float,	newbonus float)

Step 1
I will now create a trigger that will do nothing but make a copy for me of the inserted and deleted tables.

create trigger tu_employee on employee for updateas	select * into myinserted	from inserted	select * into mydeleted	from deleted

The above code, when triggered by an update statement, will create a new table called myinserted and mydeleted and populate it with the contents of the inserted and deleted tables. After creating the trigger, I execute some code to update the employee table.

update employeeset salary = salary + 5000, bonus = salary * .05, grade = grade + 1

After the update I can verify the contents of the myinserted and mydeleted tables using by simply executing a SELECT * from each table.

Step 2
I will now use the myinserted and mydeleted table in creating the code for my trigger. I would like to record the old and new information in my audit table. The old information will be in the mydeleted table. The new information will be in the myinserted table.

insert into au_salarychange( 	empid,		datechanged,	who,					prevsalary,	newsalary,	prevgrade,				newgrade,	prevbonus,	newbonus			    )(select				myinserted.empid, getdate(),	user_name(),				mydeleted.salary, myinserted.salary, mydeleted.grade,				myinserted.grade, mydeleted.bonus, myinserted.bonusfrom myinserted,mydeletedwheremyinserted.empid = mydeleted.empid)

Step 3
After verifying that the code is correct by examining the contents of the au_salarychange table, I then place the code into the trigger. Notice that all I have to do is to take out the “my” from each table name.

drop trigger tu_employee gocreatetrigger tu_employee on employee for update asif(columns_updated()& 56) >  0insert into au_salarychange( 	empid,		datechanged,	who,					prevsalary,	newsalary,	prevgrade,				newgrade,	prevbonus,	newbonus    )(select				inserted.empid, getdate(),	user_name(),				deleted.salary, inserted.salary, deleted.grade,				inserted.grade, deleted.bonus, inserted.bonusfrom inserted,deletedwhereinserted.empid = deleted.empid)

Version 7 Enhancements
In the above example, an update event would cause the trigger to fire no matter what field in the employee table was changed. However, for my purposes, I don’t care about changes such as department, name, or address.

In version 6, a function update (column_name) existed that was used to check whether a particular column had been updated. However, in our example this would require stringing together a series of such functions in an IF statement—one for each relevant column.

Version 7 provides a fast and efficient method for performing such checks with the addition of the columns_updated function. The function returns a bit mask with the bits set to 1 for each column changed. The output of the function is compared with a mask that you supply to check for the columns you are interested in.

In our example I am only interested in salary, bonus, and grade. Therefore I calculate that the proper mask is 56.

Column	Multiplier	Do I care?	Resultempid	1		0		0deptid	21		0		0name	22		0		0salary	23		1		8bonus	24		1		16grade	25		1		32Total					56

Here is the modified trigger.

create trigger tu_employee on employee for update asif(columns_updated()& 56) >  0insert into au_salarychange( 	empid,		datechanged,	who,					prevsalary,	newsalary,	prevgrade,				newgrade,	prevbonus,	newbonus			    )(select				inserted.empid, getdate(),	user_name(),				deleted.salary, inserted.salary, deleted.grade,				inserted.grade, deleted.bonus, inserted.bonusfrom inserted,deletedwhereinserted.empid = deleted.empid)go

Version 7 also allows more than one trigger for each event. However, it is not good programming practice to spread your logic among multiple triggers. Microsoft has implemented this feature to allow its developers to use triggers in support of merge replication without having to affect or modify user-defined triggers.