CODEWITHSUNDEEP
sqlsql-server-2008triggers
MeTa
MeTa

Reputation: 89

SQL Trigger update another table

I am newbie to triggers... can anybody help me with a trigger?

I have Table:

Name | Number

I want to write a trigger when my table receives a query like

update MyTable 
set Number = Number + 1 
where Name = 'myname'

When this query is running, the trigger should update another table for example:

Update MyTable 2 
set Column = 'something' 
where Name = 'myname (above name)

Thank you very much !

Upvotes: 2

Views: 16683

Answers (2)

Farrokh
Farrokh

Reputation: 1167

create a trigger on table 1 for update:

CREATE TRIGGER dbo.update_trigger 
   ON  table1
   AFTER UPDATE
AS 
BEGIN
    DECLARE @Name VARCHAR(50)
    SELECT @Name=Name FROM INSERTED
    Update MyTable 2 
        SET Column = 'something' 
        WHERE Name = @Name
END
GO

try this ;)

Upvotes: 1

marc_s
marc_s

Reputation: 754478

You will need to write an UPDATE trigger on table 1, to update table 2 accordingly.

Be aware: triggers in SQL Server are not called once per row that gets updated - they're called once per statement, and the internal "pseudo" tables Inserted and Deleted will contain multiple rows, so you need to take that into account when writing your trigger.

In your case, I'd write something like:

-- UPDATE trigger on "dbo.Table1"
CREATE TRIGGER Table1Updated
ON dbo.table1 FOR UPDATE
AS 
BEGIN
    -- update table2, using the same rows as were updated in table1
    UPDATE t2
    SET t2.Column = 'something'
    FROM dbo.Table2 t2
    INNER JOIN Inserted i ON t2.ID = i.ID        
END
GO

The trick is to use the Inserted pseudo table (which contains the new values after the UPDATE - it has the exact same structure as your table the trigger is written for - here dbo.Table1) in a set-based fashion - join that to your dbo.Table2 on some column that they have in common (an ID or something).

Upvotes: 8

Related Questions