CODEWITHSUNDEEP
sql-servertriggers
Luis Sérgio
Luis Sérgio

Reputation: 649

SQL SERVER TRIGGER USING UPDATED VALUES

I what to use a trigger to salve the dates of all the STATUS changes in my TICKETS table: I did try it there is above, but it is responding: "Invalid object name 'UPDATED'".

CREATE TRIGGER TG_TICKETS_STATUS_HISTORY
ON TICKETS_TI AFTER UPDATE 
AS 
BEGIN

    DECLARE @ID INT
    DECLARE @STATUS INT

    SELECT @ID = ID, 
        @STATUS = STATUS
    FROM UPDATED 


    IF (@STATUS != (
        SELECT TOP 1 SH.STS_NAME
        FROM TICKETS_STATUS_HISTORY SH
        WHERE SH.ID = @ID
        ORDER BY SH.STS_DATE DESC
    ))
    BEGIN
        INSERT INTO TICKETS_STATUS_HISTORY
        VALUES (@ID, @STATUS, GETDATE())
    END
END

Upvotes: 1

Views: 190

Answers (2)

LittleSweetSeas
LittleSweetSeas

Reputation: 7054

You should use INSERTED table instead of UPDATED, as update operations consist of a delete and an insert operation.

In DELETED table you'll find the old values, in INSERTED you'll find the new (updated) values.

Also, in your trigger declaration, you should use FOR keyword instead of ON referring to actions which should start the trigger.

Eg.

CREATE TRIGGER TG_TICKETS_STATUS_HISTORY
ON TICKETS_TI 
FOR UPDATE 
AS 
BEGIN    
    INSERT INTO TICKETS_STATUS_HISTORY
    SELECT I.ID, I.STATUS, GETDATE()
    FROM INSERTED I
    INNER JOIN DELETED D 
       ON I.ID = D.ID 
       AND I.STATUS != L.STATUS
END

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

What you should have is something like:

CREATE TRIGGER TG_TICKETS_STATUS_HISTORY
ON TICKETS_TI AFTER UPDATE 
AS 
BEGIN

INSERT INTO TICKETS_STATUS_HISTORY (ID,STS_NAME,STS_DATE)
select i.ID,i.Status,GETDATE()
from inserted i
    inner join (
     select ID,STS_NAME,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY STS_DATE desc) as rn
     from TICKETS_STATUS_HISTORY
    ) h
      on
         i.ID = h.ID and
         i.STATUS != h.STS_NAME and
         h.rn = 1

END

inserted (and deleted) is a pseudo-table that can contain 0, 1 or multiple rows. Assigning column values from it into a scalar variable (e.g. 

SELECT @ID = ID, 
    @STATUS = STATUS
FROM INSERTED

) is just plain wrong.

If you just want to check for whether the status column value has changed (and don't need to lookup in the history table) we can make it even simpler:

INSERT INTO TICKETS_STATUS_HISTORY (ID,STS_NAME,STS_DATE)
select i.ID,i.Status,GETDATE()
from inserted i
    inner join
     deleted d
      on
         i.ID = d.ID and
         i.STATUS != d.STATUS

Upvotes: 2

Related Questions