1

I am using this trigger:

create trigger UpdateCustomerOnWholeSellerIdChange
on Invoice after update
as
begin
    set nocount on;
    if update(WholeSellerId)
    begin
        update Customer
        set Customer.WholeSellerId = d.WholeSellerId
        from deleted d
        where d.CustomerId = Customer.CustomerId
    end
end

What I am trying to accomplish is: each CustomerId has his own WholeSellerId meaning each Customer has their own salesman. In the Invoice table, each Invoice has its own CustomerId and WholeSellerId.

So when I update the WholeSellerId in the Invoice table, I would like to update the WholeSellerId of the Customer table as well to the WholeSellerId of the Invoice table.

With my code above, even when I update my WholeSellerId in the Invoice table, it doesn't update the Customer's WholeSellerId.

Your help is appreciated.

1
  • What's meant to happen if an UPDATE affects more than one invoice for the same customer and updates WholeSellerId to different values? Commented Feb 4, 2016 at 9:05

1 Answer 1

4

You are using deleted table as source, which contain data BEFORE your update run. Replace it with inserted table which have the new data.

MSDN - Use the inserted and deleted Tables

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.