Tuesday, July 05, 2005

Triggers: instead-of a superset of before?

Towards the end of last year I had a flaming argument with a friend over the merits of SQL Server. One of his counterarguments was that instead of triggers are an adequate substitute for before triggers. Here's an example of why this is not the case.

Consider the following fairly typical design. We have an Travel table with a cascade update foreign key on Employees and Agents. There is a TravelEmployee table, cascade delete FK against Travel. When TravelEmployee records are deleted, a log table is updated by a trigger, using certain information from the Travel table (travel agent, for example).

The problem is that occasionally an entire Travel record is deleted, and when this happens, the corresponding TravelEmployee records are deleted, but the log table is not updated. Surprise! What's happening is that the trigger on TravelEmployee is doing something like this:

insert into TravelLog (EmployeeCode, TravelAgent, Action)
select EmployeeCode, TravelAgent, 'Cancelled' as Action
from deleted inner join Travel on deleted.TicketNumber = Travel.TicketNumber

Of course, by the time this trigger is executed, the record from Travel that contains the TravelAgent field is long gone, so the join produces nothing. The correct solution to this problem is to change the trigger to a before trigger. But SQL Server does not support this. How about an instead of trigger, then? Ah, but TravelEmployee has a cascade-update foreign key, and so SQL Server rightly refuses to create an instead of trigger on the grounds that it cannot then guarantee referential integrity.

Oops. There are a variety of less-than-satisfactory solutions to this problem. What we ended up doing was dropping the foreign key constraint on TravelEmployee and maintaining referential integrity by other means. Another possible solution is to create a new table containing deleted Travel records, which can then be used by the above trigger. There are a few more solutions, which I'm sure you can think of. All of these solutions have flaws.

It's cold comfort, but I was quite right: instead of is no substitute for before.


Anonymous Anonymous said...

Reading your blog and I figured you'd be interested in advancing your life a bit, call us at 1-206-339-5106. No tests, books or exams, easiest way to get a Bachelors, Masters, MBA, Doctorate or Ph.D in almost any field.

Totally confidential, open 24 hours a day.

Hope to hear from you soon!

4:29 PM  
Anonymous Anonymous said...

I wouldn't have part taken in your debate. Mainly to my pet hate of triggers in general. The become a nightmare to manage and to track bugs and I see no reason for them. Have a data access layer with stored procs or an intelligent object model to handle your data. This then enforces your rules (other than the hard constrains you set on the tables).

You can then see explicitly what is going on and have dont have the problems.

I keep the same philosophy when using events in code, unless the events give me a big advantage (such as normal UI stuff) I tend not to use them.

Brett Mahon

7:09 PM  

Post a Comment

<< Home