Trigger - update other columns in the table 1

[PM2000]

Hi,

Maybe I'm a bit of a newbie on this one, but I can't figure this out.

I have an old application that updates a table in the database (the app was not written by me and I don't have the code, which is why I need to use a trigger to do this.) This application will update column 'value' with a number. I need a trigger to add the same number to column 'originalvalue' on insert only (not on update).

So, if the app puts 1,354 in column 'value', my trigger needs to insert 1,354 in column 'originalvalue'. When column 'value' is updated in the future, I do not want it to update column 'originalvalue'.

Can anyone help?

Thanks
Peter

 

[mrdenny]

Not a problem, that is very easy to do with a trigger.

create trigger dbo.trg_{table_name}_i on {table_name}
for insert
as
update {table_name}
set {table_name}.originalvalue = inserted.value
from inserted
where {table_name}.{primary_key} = inserted.{primary_key}

What you will need to do to make this work is:
[ul]
[li]Change {table_name} to the name of your table.[/li]
[li]Change {primary_key} to the name of the field that is the primary key.[/li]
[/ul]
This trigger will only fire when a new record is inserted.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[PM2000]

Thanks Denny,

I knew it would be fairly easy, I just couldn't get it to work when I tried. Works fine now!

Peter

 

[donutman]

Give that man a star!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[PM2000]

already done!