CODEWITHSUNDEEP
sqlsql-servertriggers
s.k.paul
s.k.paul

Reputation: 7301

Create a trigger that inserts old values into other table when a row is updated

I'm trying to implement a trigger on a SQL Server database, which will insert a new row in table2 whenever a row is updated in table1. I want to get the row data (table1) before update.

For example, I have the following rows:

 table1:                          table2:
 ________________________         ________________________
 |  id       |    name  |         |  id    |     name    |
 ________________________         ________________________
 |   1       |    John  |         
 ------------------------

Currently I'm using the following trigger-

CREATE TRIGGER triggername
ON table1
FOR UPDATE
AS
Begin
  INSERT INTO table2(name) 
     SELECT i.name 
     FROM Inserted i
End

This trigger is doing the following -

 table1:                          table2:
 ________________________         ________________________
 |  id       |    name  |         |  id    |     name    |
 ________________________         ________________________
 |   1       |    Alex  |         |   1    |      Alex   |
 ------------------------         ------------------------

But I want 'John' in table2. For example, table1 and table2 now should be as follows-

 table1:                          table2:
 ________________________         ________________________
 |  id       |    name  |         |  id    |     name    |
 ________________________         ________________________
 |   1       |    Alex  |         |   1    |      John   |
 ------------------------         ------------------------

How can I do that?

Upvotes: 2

Views: 187

Answers (3)

Code It
Code It

Reputation: 396

use the following-

 Begin
   INSERT INTO table2(name) 
   SELECT d.name 
   FROM Deleted d
 End

Upvotes: 1

WorkSmarter
WorkSmarter

Reputation: 3808

This will only return the new value:

INSERT INTO table2(name) 
SELECT i.name 
FROM Inserted i

This however will return the value before the update

INSERT INTO table2(name) 
select d.name
from inserted i
  join deleted d
    on (i.id = d.id)

Upvotes: 1

marc_s
marc_s

Reputation: 755391

You need to use the Deleted pseudo table in your trigger, which contains the old values (before the UPDATE):

CREATE TRIGGER triggername
ON table1
FOR UPDATE
AS
Begin
  INSERT INTO table2(name) 
     SELECT d.name 
     FROM Deleted d
End

The Inserted pseudo table contains the new values - after the UPDATE.

Upvotes: 1

Related Questions