Trigger to update BEFORE column constraints are checked
Posted on 2010-09-16
I have the following problem: one of my table fields, record_dttm, is updated via trigger every time a new record is created/updated, storing the current getdate() in it. Essentially, the field will never be empty: every time a record is modified, the trigger will automatically generate a new value.
It would then make sense to specify the field as NOT NULL, but this is currently impossible for me: if I try to insert a record that does not specify it, the Database complains that record_dttm cannot be null, BEFORE my trigger can actually fill it.
So, in short: how can I write a trigger so that it updates the value of record_dttm *before* SqlServer checks whether the field is null or not?