CODEWITHSUNDEEP
sqltriggers
user838359
user838359

Reputation: 173

using an update trigger

I have been trying to use an update trigger which checks for the title_id in the primary table. This is what i did:

ALTER TRIGGER [dbo].[EX6] on [dbo].[sales]
   INSTEAD OF UPDATE NOT FOR REPLICATION
AS
BEGIN 
   IF EXISTS(SELECT S.title_id 
             FROM inserted S
             INNER JOIN Titles T On T.title_id = S.title_id)
   BEGIN 
      UPDATE Sales 
      SET sales.stor_id = inserted.stor_id
         , sales.ord_num = inserted.ord_num
         , sales.title_id = inserted.title_id
          , sales.ord_order = inserted.ord_order,
         ,sales.qty = inserted.qty
   END

However, when i execute this, I am getting the following errors:

     the multi-part identifier inserted.stor_id could not be bound

The same error occurs for the rest of the columns used in update command. So, can u please help me out in solving this and successfully update the column in the database..

Upvotes: 1

Views: 100

Answers (1)

Adam Wenger
Adam Wenger

Reputation: 17560

You have aliased inserted to be S so you will need to continue that in your UPDATE statement

ALTER TRIGGER [dbo].[EX6] on [dbo].[sales]
   INSTEAD OF UPDATE NOT FOR REPLICATION
AS
BEGIN 
   IF EXISTS(SELECT S.title_id 
             FROM inserted S
             INNER JOIN Titles T On T.title_id = S.title_id)
   BEGIN 
      UPDATE st
      SET st.stor_id = S.stor_id
         , st.ord_num = S.ord_num
         , st.title_id = S.title_id
         , st.ord_order = S.ord_order
         , st.qty = S.qty
      FROM Sales AS st
      INNER JOIN inserted S ON st.stor_id = S.stor_id
   END

I have updated the answer to reflect changes discussed in chat.

Upvotes: 2

Related Questions