Using a Trigger to Insert updated columns only

[Bwintech]

Hi

I want to use a trigger to insert a row into a new table if one particular column (within that row) has been updated.

How do I code the SQL SYNTAX for Insert 'Updated Row into new table' within the body of the trigger?

Thank you kindly for your help.

Brett

 

[osjohnm]

Hi

you will need to use the inserted table.

try something like this

CREATE TRIGGER TEST on dbo.CATEGORY
FOR INSERT, UPDATE
AS

insert into category_audit(category_code, category_name,
date_updated)
select c.category_code, c.category_name, c.date_updated
from category c, inserted i
where c.category_code = i.category_code
GO

on second thoughts you could rpobably just query the inserted tables straight like so:

insert into category_audit(category_code, category_name,
date_updated)
select category_code, category_name, date_updated
from inserted

the inserted table stores the current changed row while the trigger executes so you might be able to avoid the join.

Hope this helps

John

 

[Bwintech]

Great! Thanks John. Works perfectly.

What if I want to only insert that row if column A has been updated? Where do I add this to the insert statement?

 

[osjohnm]

Will only column A be updated or at times might it be column B or column C?

John

 

[Bwintech]

Thanks John

Any column may be updated, I only want the trigger to fire if column A is updated though..

 

[osjohnm]

Hi

In that case then change it like so:

CREATE TRIGGER TEST on dbo.CATEGORY
FOR UPDATE
AS

if update(column A)
insert into category_audit(category_code, category_name,
date_updated)
select c.category_code, c.category_name, c.date_updated
from category c, inserted i
where c.category_code = i.category_code
GO

if you want to record new records inserted which would affect column A then change it to a FOR INSERT, UPDATE trigger.

hope that helps

John