>I have a requirement to define 2 columns in ddl as NOT NULL one gets a default constraint but the other can't be added until it is set to a value from the same record.
>
>So I have
>UpdateBy nvarchar(128) NULL
>UpdateTime datetime NOT NULL
>
>I then update UpdateBy setting it equal to CreateBy and then altering the column and setting it not null.
>
>This works just fine.
>
>the problem is with updatetime. they want me to use the servers time instead of the users time, so they don't want me updating that field in the ui, so it gets passed in as null.
>
>It is my understanding that the update of the record that the constraint fires before the trigger does. How do you solve this?
AFTER UPDATE triggers fire after the update happens (but in the same transaction). So, create an AFTER UPDATE trigger to update your UpdateTime column with CURRENT_TIMESTAMP and all should be good.
If it's not broken, fix it until it is.
My Blog