Trigger not updating correct row

,
1

I'm trying to write a trigger that get a QuoteNumber from table 1 and updates it into table 2 where ShippingIdentity matches the records in both tables. The problem is the QuoteNumber is being inserted into it's own row(record) Can anyone please let me know why? Thanks.

ALTER TRIGGER ShippingQuoteNumber
ON ShippingInfo
after UPDATE
AS
BEGIN

UPDATE ShippingInfo SET QuoteNumber = a.QuoteNumber
FROM AccountInfo a
INNER JOIN inserted t ON  t.ShippingIdentity = a.ShippingIdentity

END

2 
ALTER TRIGGER ShippingQuoteNumber
ON dbo.ShippingInfo
AFTER UPDATE
AS
SET NOCOUNT ON;
UPDATE si
SET QuoteNumber = a.QuoteNumber
FROM dbo.AccountInfo a
INNER JOIN inserted t ON t.ShippingIdentity = a.ShippingIdentity
INNER JOIN dbo.ShippingInfo si ON si.ShippingIdentity = t.ShippingIdentity
3

Thanks for your reply. But the QuoteNumber in the ShippingInfo table shows a NULL value.

4

Hmm, yeah, that's almost certainly not the correct join to AccountInfo. While inserted and ShippingInfo should join on ShippingIdentity, the AccountInfo table should join on a different column.

FROM dbo.AccountInfo a
INNER JOIN inserted t ON t.? = a.?
INNER JOIN dbo.ShippingInfo si ON si.ShippingIdentity = t.ShippingIdentity

5

Maybe this will clear thing up. I have no value in the ShippingInfo table column QuoteNumber. I have a column in both tables with the same ShippingIdentity value and I'm trying to put the same QuoteNumber into ShippingInfo table where the ShippingIdentity and QuoteNumber will match in both tables

6

But this trigger fires when a new row is inserted into ShippingInfo ... how could a row with this brand new identity value, just now being inserted, already be in the AccountInfo table?? The tables must link up by AccountNumber / AccountId / CustomerId / other, right?