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.

closed as unclear what you're asking by mustaccio, RLF, Lennart, RolandoMySQLDBA, Max Vernon May 7 '16 at 4:10

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.