CODEWITHSUNDEEP
sqlsql-serverdatabasetriggers
user1287453
user1287453

Reputation: 279

Trigger After Update SQL

I have Customer table. To simplify lets say i have two columns

Id  
Name

I have a second table (Log) that I want to update ONLY when the Id column of my customer changes. Yes you heard me right that the primary key (Id) will change!

I took a stab but the NewId that gets pulled is the first record in the Customer table not the updated record

 ALTER TRIGGER [dbo].[tr_ID_Modified]
    ON [dbo].[customer]
 AFTER UPDATE
 AS
 BEGIN
   SET NOCOUNT ON;
   IF UPDATE (Id) 
     BEGIN
        UPDATE [log]
        SET NewId = Id
        FROM customer
     END
  END

Upvotes: 0

Views: 2719

Answers (1)

Dave Mason
Dave Mason

Reputation: 4936

Many would make the argument that if you are changing PK values, you need to rethink the database/table design. However, if you need a quick & dirty fix, add a column to the customer table that is unique (and not null). Use this column to join between the [inserted] and [deleted] tables in your update trigger. Here's a sample script:

CREATE TABLE dbo.Customer (
    Id INT CONSTRAINT PK_Customer PRIMARY KEY,
    Name VARCHAR(128),
    UQColumn INT IDENTITY NOT NULL CONSTRAINT UQ_Customer_UQColumn UNIQUE
)

CREATE TABLE dbo.[Log] (
    CustomerId INT NOT NULL,
    LogMsg VARCHAR(MAX)
)

INSERT INTO dbo.Customer
    (Id, Name)
VALUES
    (1, 'Larry'),
    (2, 'Curley'),
    (3, 'Moe')

INSERT INTO dbo.[Log]
    (CustomerId, LogMsg)
VALUES
    (1, 'Larry is cool'),
    (1, 'Larry rocks'),
    (2, 'Curley cracks me up'),
    (3, 'Moe is mean')


CREATE TRIGGER [dbo].[tr_Customer_Upd]
ON [dbo].[customer]
FOR UPDATE
AS
BEGIN

UPDATE l
SET CustomerId = i.Id
FROM inserted i
JOIN deleted d
    ON i.UQColumn = d.UQColumn
JOIN [Log] l
    ON l.CustomerId = d.Id
END

SELECT *
FROM dbo.[Log]

UPDATE dbo.Customer
SET Id = 4 
WHERE Id = 1

SELECT *
FROM dbo.[Log]

Upvotes: 1

Related Questions