CODEWITHSUNDEEP
sql-server
Ibanez1408
Ibanez1408

Reputation: 5068

Update a column of one table when a column is updated on another table with parameters using trigger

I have 2 tables that have a relationship through CustomerId:

Invoices:

Invoice | RouteCode | CustomerId
1001    | 1         | 90
1002    | 2         | 70
1003    | 3         | 80

Customers:

CustomerId | CustomerName | RouteCode 
90         | AAA          | 1
70         | BBB          | 2
80         | CCC          | 3

I would like to create a trigger on which once the RouteCode in the Invoices is changed, the routecode in the Customers for the particular Customer will be changed.

Example:

Invoices:

Invoice | RouteCode | CustomerId
1001    | 2         | 90           (the RouteCode here has changed)
1002    | 2         | 70
1003    | 3         | 80

Customers:

CustomerId | CustomerName | RouteCode 
90         | AAA          | 2        (the RouteCode here must change too)
70         | BBB          | 2
80         | CCC          | 3

I have this code:

create trigger UpdateRouteCode
before update on Invoice
for each row
begin
    if :new.RouteCode != :old.RouteCode
    then
        update Customer c
        set c.RouteCode = :new.RouteCode
        where c.CustomerId = :new.CustomerId
end

I don't know if this is right because in the new query window it says:

Msg 102, Level 15, State 1, Procedure UpdateRouteCode, Line 2
Incorrect syntax near 'before'.

Upvotes: 0

Views: 28

Answers (1)

Squirrel
Squirrel

Reputation: 24783

create trigger UpdateRouteCode
on  Invoices
for update
as
begin
    update c
    set    RouteCode = i.RouteCode  
    from   inserted i
           inner join Customers c on i.CustomerId = c.CustomerId
    where  i.RouteCode <> c.RouteCode 
end

Upvotes: 3

Related Questions