CODEWITHSUNDEEP
sqlsql-server
Jerome Landis
Jerome Landis

Reputation: 57

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

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

Upvotes: 0

Views: 3448

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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

Upvotes: 3

Related Questions