Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

The Trouble with Triggers, Part III

Part 3 of a three-part article on triggers in SQL Server. It provides an approach to coding and debugging triggers and discusses the features that added in SQL Server version 7 regarding triggers.


advertisement
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 employee go create table employee ( empid int identity(1,1) constraint pkemployee primary key (empid), deptid int, name varchar(30), salary float, bonus float, grade int ) go if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'au_salarychange') drop table au_salarychange go create 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 update as 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 employee set 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.bonus from myinserted,mydeleted where myinserted.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 go create trigger tu_employee on employee for update as if (columns_updated()& 56) > 0 insert 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.bonus from inserted,deleted where inserted.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? Result empid 1 0 0 deptid 21 0 0 name 22 0 0 salary 23 1 8 bonus 24 1 16 grade 25 1 32 Total 56

Here is the modified trigger.

create trigger tu_employee on employee for update as if (columns_updated()& 56) > 0 insert 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.bonus from inserted,deleted where inserted.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.



   
Joe Lax has spent more than 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. He also is a MCSE and an MCT who recently has started to learn Oracle, which affords him no end of fun. He loves feedback and can be reached here.
Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date