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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Scott PletcherSenior DBACommented:
For one column you don't need to worry about.  Something like above will do what you need.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.