SQL Trigger update another table

Question

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 !

Solution 1

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

Solution 2

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

Comments

[MeTa]

and how i can identify "where" variable? After Begin

[Farrokh]

decalrea a variable and then "select @name=name from inserted", and in your where clause use it.need example?

[marc_s]

This has a fundamental flaw! You seem to assume the trigger is called once per row - this is NOT the case with SQL Server. If your UPDATE affects multiple rows, the trigger will be called once per statement, and the pseudo tables Inserted and Deleted can contain multiple rows. So which of these rows are you selecting when doing SELECT @Name=Name FROM INSERTED ??? It's not defined - you get one, arbitrary row and all other are ignored.... you need to re-write your trigger to handle multiple rows in Inserted and Deleted !