MeTa MeTa - 2 years ago 131
SQL Question

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 !

Answer Source

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"
ON dbo.table1 FOR UPDATE
    -- 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        

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download