Getting identity value in a trigger
Hello,
I have a column in a table that i want to update with the current date and time every time the row is updated (or inserted).
Basically this is a last updated datetime column and used by another process to find recently updated rows in the database.
To avoid a minumim amount of changes in our legacy system i thought i would add a trigger on INSERT and UPDATE that would find the updated row and update the column.
However the only way i can find the row is by its identity column as this is the only column that is unique in the table.
It appears that it is impossible to get the value of the identity column in a trigger. Is this correct?
And triggers should be written to handle multiple rows so using scope_identity would not be a solution?
So is there a solution? Either using a trigger, or some other mechanism that ensures that the column is always updated with the cirrent date and time?
I have a column in a table that i want to update with the current date and time every time the row is updated (or inserted).
Basically this is a last updated datetime column and used by another process to find recently updated rows in the database.
To avoid a minumim amount of changes in our legacy system i thought i would add a trigger on INSERT and UPDATE that would find the updated row and update the column.
However the only way i can find the row is by its identity column as this is the only column that is unique in the table.
It appears that it is impossible to get the value of the identity column in a trigger. Is this correct?
And triggers should be written to handle multiple rows so using scope_identity would not be a solution?
So is there a solution? Either using a trigger, or some other mechanism that ensures that the column is always updated with the cirrent date and time?
Nakul Vachhrajani
Why go down the path of triggers? If the application does not refer this column in any way, you can use default constraint to populate the dates.
ASKER
ok i thought there maybe another solution.
How can i use a contrataint to always update a column with the last datetime it has been updated? So not just on insert but also on every update.
/thanks.
How can i use a contrataint to always update a column with the last datetime it has been updated? So not just on insert but also on every update.
/thanks.
ASKER
however i was thinking i could do it with a combination of default value for insert, and a trigger for updates - because then i have the value of the identity column.
The inserted "table" will give you the rows that were INSERTed/UPDATEd, including the identity column. You can update the row by joining to the inserted table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.