Not NULL happens before a Trigger, right?

[gaffonso]

I'm writing a trigger to write audit data to my table, it automatically inserts the LastModifiedID and LastModified date into fields on that table.

I've put a Not NULL constraint on the LastModifiedID and LastModified columns.

The problem is that a simple insert into the other columns (not the LastModified columns) fails with a "fields can't be null". I was expecting the trigger to take care of the insertion of data into the LastModified columns (the trigger code does this) and therefore to satisfy the Not Null constraint.

My conculsion is that the Not Null constraints are checked BEFORE the trigger runs so my normal insert (which doesn't insert into the LastModified columns directly) fails before the trigger has a chance to insert into the LastModified columns.

Is that the right conclusion? Any suggestions? Obviously I can remove the Not Null constraint but that just doesn't feel right.

Thanks!

- Gary

 

[tlbroadbent]

Constraints are checked before After Triggers are fired. As you've seen, when the constraint fails, the transaction fails and the trigger doesn't fire. SQL 2000 introduces Instead Of Triggers. These fire before constraints. If you are using SQL 2000, you may want to investigate. Otherwise, you'll have to remove the constraint and enforce the rule in the trigger. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.