John Baird
Coatesville, Pennsylvania, United States
General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Environment versions
SQL Server:
SQL Server 2008 R2
Network:
Windows 2008 Server
>>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.
but won't the not null constraint cause an error if it tries to update with a null value coming in?
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only