I need to update a log table with old and new updated values. Any ideas how can we do this in a trigger?

I need to log all inserts and updates and write a custom message in the column like.. "User John was added by Mark". " Department ABC was updated to xyz in table Department by lily" It's only this column that needs to be updated by trigger actually. All updates and inserts on the table needs to get logged in another log table.

  • Thank you, but I got the answer from Arthur. Please reopen the question for discussion. – HappieFeet Aug 14 '17 at 13:53

Yes, you can do this inside a trigger. A trigger can reference the objects INSERTED and DELETED which can be used as tables to tell what information is inserted and deleted. When an update is made, the old rows are "deleted" and the new rows are "inserted."

Write your SQL statement to INSERT all the rows from INSERTED and DELETED into your log table.

I think you should look carefully at the business function this provides, since a logging table will grow rapidly in size, and updates can update multiple rows at once, so make sure your trigger can handle multiple rows.

Not the answer you're looking for? Browse other questions tagged or ask your own question.