Trigger : after update insert into second table

ik · December 5th, 2008, 07:21 PM

I have two tables in SQL Server firsttable and secondtable.
I would like to make an trigger in firsttable that after update some column to insert some values (from firsttable) into secondtable.
I have tried to read updated row id with @@identity, but does not works.
Please, help.

josem29 · January 28th, 2009, 12:00 PM

I am having the same problem. I was asked to create a log table that will collect changes in few fields from 1st table.

rstelma · January 28th, 2009, 12:52 PM

Can you post your code? What you have so far?

Peso · January 29th, 2009, 07:45 AM

Code:

CREATE  TRIGGER dbo.trgFirstTable_Update
ON dbo.FirstTable
AFTER  UPDATE
AS
IF  UPDATE(Col1)ORUPDATE(Col2)
INSERT SecondTable
(
SomeCol
)
SELECT ft.SomeCol
FROM dbo.FirstTable AS ft
INNER JOIN inserted AS i ON i.pkCol = ft.pkCol

Jeff Moden · January 30th, 2009, 12:32 AM

... and don't ever use @@IDENTITY especially when triggers are involved. Use Scope_Identity() instead.