CODEWITHSUNDEEP
sql-servertriggers
James
James

Reputation: 630

SQL Server timestamping trigger

My knowledge of SQL is pretty limited as I mostly focus in backend Ruby development. However, due to architectural changes and wanting to keep things well designed; I have decided to set up timestamping on the database level rather than on the backend level.

As it goes right now, all of my tables have two columns: CreatedAt and UpdatedAt, both with a default value of GETDATE().

However, I now need to set up a timestamping trigger for UpdatedAt, so that every time a row (or rows) are updated, the UpdatedAt column for those rows gets a brand new timestamp.

I am having trouble with the following trigger I wrote. I am getting an error:

Incorrect Syntax near '='

I am testing out my trigger on my Orders table first, and then I plan to move the functionality to all tables.

CREATE TRIGGER dbo.trgTimestampAfterUpdate
ON Dbo.Orders
AFTER UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted)
    BEGIN
        SET UpdatedAt = GETDATE()
    END
END

I know that I can access the inserted, and deleted virtual tables when using a trigger. My thought with this query was that I would use inserted in order to distinguish which rows have been updated. If anyone can help that would be great, and also if you wouldn't mind explaining to me what I messed up with my syntax or line of thinking would be greatly appreciated.

Upvotes: 1

Views: 212

Answers (1)

Code Magician
Code Magician

Reputation: 24022

You can't really access the inserted tables quite like that. Having just Set UpdatedAt =... is an incomplete statement. Implicitly it makes sense to you but even in your trigger, you have to make complete SQL statements.

The way to do this is to JOIN to the INSERTED table (in the example below, I'm using a semi-join) You can then use the contents of the INSERTED table to perform another update. 

CREATE TRIGGER [dbo].[trgTimestampAfterUpdate] ON dbo.orders
    FOR UPDATE
AS
BEGIN
    IF NOT(UPDATE(UpdatedAt)) --Avoid triggers firing triggers
    BEGIN

        UPDATE  dbo.orders
        SET     UpdatedAt = GETDATE()
        WHERE   id IN ( SELECT id
                        FROM   inserted )

    END
END

Two REALLY important things to note in this code example. First Updating the table with the trigger on it will cause the trigger to fire again (creating a loop that will increase until you reach the max level of nested triggers on your system.) I put a check to make sure it terminates if you're only updating the updatedat column.

Second, never ever assume there is only one row in the inserted table. Something like the code below is a very common mistake

DECLARE @id INT
SELECT @id = id FROM INSERTED 

UPDATE MyTable 
SET UpdatedAT = GETDATE() 
WHERE id = @id
--DON'T DO THIS!

This looks right, and is a common mistake, but it will only ever update 1 record and there could be any number of records in the INSERTED table.

Upvotes: 3

Related Questions