The Trouble with Triggers, Part III

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.