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.