updating multiple tables with triggers - SQL Server

Question

I need to update my tableA based on the insertions in tableB. i have created a trigger which fires when a row is inserted into tableB and simply copies it to tableA (which is exactly what i want). But the problem comes when i update my existing records in tableB, the rows in tableA already exist with the same primary key.

to deal with it, i tried to delete the existing row in tableA where the primary key matches for the updated row and then simply insert the row from the "inserted" table. But, tsql simply does not let me delete me from a trigger and gives an error stating "The multi-part identifier "INSERTED.Id" could not be bound."

Here is what i am trying to accomplish code-wise:

delete from dbo.CurrentItems where dbo.CurrentItems.CurrentItemId = INSERTED.Id
INSERT INTO CurrentItems
SELECT * FROM INSERTED
WHERE IsActive = 1

Any ideas guys?

Answer 1

In the DELETE FROM statement, you have to add the INSERTED pseudo-table to the tables you're operating on (in the FROM clause), like so:

DELETE dbo.CurrentItems 
  FROM dbo.CurrentItems
     , INSERTED
 WHERE dbo.CurrentItems.CurrentItemId = INSERTED.Id

INSERT INTO CurrentItems
SELECT * FROM INSERTED
WHERE IsActive = 1

Alternatively, you could use an INNER JOIN:

DELETE dbo.CurrentItems 
  FROM dbo.CurrentItems CI
       INNER JOIN INSERTED I ON CI.CurrentItemId = I.Id

Answer 2

Why can't you just simply update the existing rows in TableA when your rows in TableB get updated?? UPDATE instead of DELETE/INSERT.

You could have a AFTER INSERT trigger which does what you want now, plus an AFTER UPDATE trigger which would be updating rows in TableA (instead of inserting new ones).

Answer 3

You said you have a trigger that fires when "a row is inserted into tableB". So you have the trigger to fire on Inserts. It sounds to me like you also have the trigger to fire on Updates as well. Did you want the trigger to fire on updates as well? If not, remove "Update" from the trigger definition.

Randy