Creating a SQL trigger to put an update time in the same row after updatу of one field-CodePudding

I have a table ORDER_TEST that has columns №, STATUS and DATE_STATUS.

№ is unique for each row (primary key).

I want to create a trigger that will set (update) STATUS_TIME after STATUS was updated.

I tried to use

CREATE TRIGGER AFTER_UPDATE_STATUS
ON ORDER_TEST
AFTER UPDATE
AS
    UPDATE ORDER_TEST
    SET DATE_STATUS = {fn now()}
    FROM ORDER_TEST
    JOIN INSERTED ON ORDER_TEST.[№] = inserted.[№]
    WHERE (SELECT STATUS FROM INSERTED) <> (SELECT STATUS FROM DELETED)

But SQL Server says that changes will affect several rows.

Help please. What is wrong?

CodePudding user response：

You Need to add the Deleted table also to the Join and check if the value has changed or not.

Something like this

USE [TEST_DATABASE];
GO
CREATE TRIGGER AFTER_UPDATE_STATUS
ON ORDER_TEST
AFTER UPDATE
AS

UPDATE YourTable
    SET
        UpdateTimeStamp = GETDATE()
    FROM Inserted
        INNER JOIN Deleted 
            ON Inserted.KeyColumn = Deleted.KeyColumn
        INNER JOIN YourTable 
            ON YourTable.KeyColumn = Inserted.KeyColumn
        WHERE Inserted.Status <> Deleted.Status

CodePudding user response：

It depends on the recursion level for triggers currently set on the DB.

If you do this:

SP_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Or this:

ALTER DATABASE db_name
SET RECURSIVE_TRIGGERS OFF

That trigger above won't be called again, and you would be safe (unless you get into some kind of deadlock; that could be possible but maybe I'm wrong).

Still, I do not think this is a good idea. A better option would be using an INSTEAD OF trigger. That way you would avoid executing the first (manual) update over the DB. Only the one defined inside the trigger would be executed.

An INSTEAD OF INSERT trigger would be like this:

CREATE TRIGGER setDescToUpper ON part_numbers
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO part_numbers (colA, colB, part_description) 
    SELECT
        colA, colB,
        UPPER (part_description)
    FROM
        INSERTED
END

It depends on the recursion level for triggers currently set on the DB.

If you do this:

SP_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Or this:

ALTER DATABASE db_name
SET RECURSIVE_TRIGGERS OFF

That trigger above won't be called again, and you would be safe (unless you get into some kind of deadlock; that could be possible but maybe I'm wrong).

An INSTEAD OF INSERT trigger would be like this:

CREATE TRIGGER setDescToUpper ON part_numbers
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO part_numbers (colA, colB, part_description) SELECT
        colA, colB,
        UPPER(part_description)
    FROM
        INSERTED
END

This would automagically "replace" the original INSERT statement by this one, with an explicit UPPER call applied to the part_description field.

An INSTEAD OF UPDATE trigger would be similar (and I don't advise you to create a single trigger, keep them separated).

Also, this addresses @Martin comment: it works for multirow inserts/updates (your example does not).