Posted by Erland Sommarskog on 05/20/07 17:50

DA Morgan (damorgan@psoug.org) writes:
>> The trigger fires *after* an insert has taken place, therefore in the
>> second case the trigger rollsback because the country does now indeed
>> exist in the subquery
>>
>> Any ideas?
>
> That is one of the major problems with a product that only has AFTER
> triggers. In those products that have both BEFORE and AFTER triggers,
> AFTER triggers are used for auditing and BEFORE triggers used for
> security and integrity. Essentially you are trying to do the right
> thing, in the wrong way, in a product that truly doesn't support it.

Actually, SQL Server also has INSTEAD OF triggers, which is not really
the same thing as an BEFORE trigger. Since an INSTEAD OF trigger requires
you to redo the action that trigger it, it less apetizing for checks -
unless the check is of the kind "DELETE is not permitted on this table".

But a BEFORE trigger would not have help Shane, as his trigger seemed to
perform a check against existing data in the table. The logic appears
to be "it's OK to insert UK in the table if it's already there". His
AFTER trigger permits everything. But a BEFORE trigger would have kept
the table empty.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]