SQL Trigger Updating a different table when the data changes in another table

Question

We have two tables: one that contains products and another one that has the order information.

CREATE TABLE [dbo].[ORDERS] 
  ( 
     [ROW]           [BIGINT] IDENTITY(1, 1) NOT NULL, 
     [ID]            [UNIQUEIDENTIFIER] NOT NULL, 
     [RETAILER_ID]   [UNIQUEIDENTIFIER] NOT NULL, 
     [INDIVIDUAL_ID] [UNIQUEIDENTIFIER] NOT NULL, 
     [PRODUCT_ID]    [UNIQUEIDENTIFIER] NOT NULL, 
     [QUANTITY]      [BIGINT] NOT NULL, 
     [DATE]          [DATETIME] NOT NULL, 
     [MEMO]          [NVARCHAR](MAX) NULL 
  ) 
ON [PRIMARY] 
TEXTIMAGE_ON [PRIMARY] 

CREATE TABLE [dbo].[PRODUCTS] 
  ( 
     [ROW]         [BIGINT] IDENTITY(1, 1) NOT NULL, 
     [ID]          [UNIQUEIDENTIFIER] NOT NULL, 
     [RETAILER_ID] [UNIQUEIDENTIFIER] NOT NULL, 
     [NAME]        [NVARCHAR](255) NOT NULL 
  ) 
ON [PRIMARY]

We need to fix this Trigger so that when a name of a product changes, the memo field in the orders table needs to have a remark about that change in the orders table. So here is the trigger that we designed but we are getting errors "The multi-part identifier "o.Memo" could not be bound."

Here is the trigger that generates errors? Where did we go wrong?

CREATE TRIGGER DBO.PRODUCTS_NAME_CHG 
ON DBO.PRODUCTS 
AFTER UPDATE 
AS 
  BEGIN 
      -- SET NOCOUNT ON added to prevent extra result sets from 
      -- interfering with SELECT statements. 
      DECLARE @old NVARCHAR(255), 
              @new NVARCHAR(255), 
              @ID  UNIQUEIDENTIFIER 

      SELECT @ID = ID, 
             @old = NAME 
      FROM   DELETED 

      SELECT @NEW = NAME 
      FROM   INSERTED 

      SET NOCOUNT ON; 

      IF UPDATE (NAME) 
        BEGIN 
            UPDATE DBO.ORDERS 
            SET    o.MEMO = o.MEMO + ' ' + @OLD + ' HAS CHANGED NAME TO ' + @NEW 
                            + '. ' 
            FROM   ORDERS o 
                   INNER JOIN PRODUCTS P 
                           ON P.ID = O.PRODUCT_ID 
            WHERE  P.ID = @ID 
        END 
  END 

GO

Never assume that there will always be one record in inserted and deleted tables. Sql Server fires triggers once per statement, not per row, so this trigger will fail to notice change of name if you decide to update bunch of records at once. The join of inserted, deleted and base table will produce all the info you need to update base table. — Nikola Markovinović
– Nikola Markovinović, Commented Sep 2, 2013 at 19:49
if update(name) does not ensure that name has changed; it merely expresses that name took part in update query. This will prove problematic if you work with an ORM that updates all the columns. Use where inserted.name <> deleted.name in update query in addition to if update(name) test. If name is nullable you will have to devise more complicated criteria. — Nikola Markovinović
– Nikola Markovinović, Commented Sep 2, 2013 at 19:50

Raphaël Althaus · Accepted Answer · 2013-09-02 19:39:53Z

3

Well, the syntax for an update with an inner join should be

UPDATE o -- use alias here, not table name.
  SET o.Memo = --blabla
  FROM Orders o
  INNER JOIN --blabla

edited Sep 2, 2013 at 19:39

answered Sep 2, 2013 at 19:24

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

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

Collectives™ on Stack Overflow

SQL Trigger Updating a different table when the data changes in another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related