Trigger help Column value before and after update

i need to code a trigger that detects when the column of a table has changed and the records the before and after values for the column in a table.. How do I go about constructing the trigger (sql server 2008R2)
LVL 1
bensonwalkerAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
For one column you don't need to worry about.  Something like above will do what you need.
0
 
Scott PletcherSenior DBACommented:
Is this for a single, specific column or for multiple/all columns?

The general approach is the same, but I'd strongly urge you to dynamically generate code if it's for more than one column.

Here's the general structure for a single column:

CREATE TRIGGER table_name_TR_UPDATE
ON dbo.table_name
AFTER UPDATE
AS
SET NOCOUNT ON;
INSERT INTO dbo.log_table ( table_name, column_name, old_value, new_value )
SELECT 'table_name', 'column1', d.column1, i.column1
FROM inserted i
INNER JOIN deleted d ON
    d.key_value = i.key_value AND
    ISNULL(d.column1, '~~') <> ISNULL(i.column1, '~~')
GO
0
 
bensonwalkerAuthor Commented:
No this is one column... sorry not sure what you mean by dynamically generate code?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.