CODEWITHSUNDEEP
sql-servertriggers
Raed Alsaleh
Raed Alsaleh

Reputation: 1621

Catch modified Row(s) in SQL update trigger

How can catch the modified row(s) in update trigger to insert them into other table ?

Upvotes: 1

Views: 4078

Answers (3)

Rastislav Franka
Rastislav Franka

Reputation: 21

To change colum "name" in "MyTable2" when colum "name" are updated in "MyTable1" try something like this.

CREATE TRIGGER tr_test ON MyTable1
AFTER INSERT,UPDATE,DELETE
AS

update x set name = dsi.name
from deleted as ds
        inner join inserted as dsi on dsi.id = ds.Id and dsi.name is not null and 
ltrim(dsi.name) != '' and rtrim(ltrim(isnull(ds.name, '') )) != rtrim(ltrim(isnull(dsi.name, '') ))
    inner join MyTeble2 as x on x.name = ds.name
where ds.name is not null and ltrim(ds.name) != ''

Upvotes: 0

starko
starko

Reputation: 1149

In SQL Server we have only two virtual tables you can workwith inside a trigger, those are "INSERTED" and "DELETED". For example:

Insert Operation: When you insert a new record "INSERTED" virtual table contains the newly inserted record, where as "DELETED" virtual remains empty.

Update Operation: When you update any record, first the old record will be placed into the "DELETED" virtual table and the newly updated record is hold by the "INSERTED" virtual table.

That means you can get the old value from "DELETED" and the currently updating value through "INSERTED" virtual table. you can query them like:

  -- To get the old record value

SELECT * FROM DELETED

-- To get the updated value

SELECT * FROM INSERTED

Delete Operation: When you delete any particular record the deleted record will be inserted into the "DELETED" virtual table.

  1. SELECT * FROM UPDATED - gives ERROR.

  2. Try this:

    DECLARE @OldVal int, @NewVal int
    SELECT @OldVal = Col FROM DELETED
    SELECT @NewVal = Col FROM INSERTED

by holding the old and new values you can compare their state.

Upvotes: 3

Zohar Peled
Zohar Peled

Reputation: 82474

Use the inserted table.
Here is a very basic example:

CREATE TRIGGER <name> ON <table> FOR UPDATE
AS

INSERT INTO <otherTable> (<Columns>)
SELECT <Columns>
FROM Inserted

GO

Upvotes: 1

Related Questions