Trigger on insert and update that adds modification date

Question

I'm making a simple table with names, emails etc, but I also have a ModifiedDate. My idea is to use a trigger after both insert and update, and insert the current date. Thus if anyone does anything (except delete) to that column, the date should reflect that.

This is however not working.

CREATE TRIGGER ModDate
    ON X
    AFTER INSERT, UPDATE
AS
BEGIN

    INSERT INTO X (ModifiedDate)
    VALUES (GETDATE())

END

Now I have a couple of values that can't be null, and what this seems to do is try and create a new row. I would like it to insert the date into the row that is currently being acted upon, I have no idea how though. Also what if I add 5 rows at once ?

Of course it is creating a new row, you have an INSERT statement. If you want to intercept the insert and/or update to change one of the values you would need to use an INSTEAD of trigger. Then to handle the multiple row operations you need to create your trigger code so that it references the inserted virtual table. I would recommend using two trigger rather than one. Or even better would be to put this logic in your procedure. — Sean Lange, Mar 11 '15 at 14:13
I was assuming you use procedures for updating and inserting but that might not be the case. :) — Sean Lange, Mar 11 '15 at 14:30

jpw · Accepted Answer · 2015-03-11T14:23:01.713

15

You need to join the inserted virtual table in the trigger to limit the rows that get updated to those actually changed. Try this:

CREATE TRIGGER ModDate
    ON TableX
    AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE X 
    SET ModifiedDate = GETDATE()
    FROM TableX X
    JOIN inserted i ON X.key = i.key -- change to whatever key identifies 
                                     -- the tuples
END

Like @ZoharPeled correctly pointed out in a comment below there's really not much point in having the trigger update the date on insert - it would be better to use getdate() as the default value on the column (or even as another column InsertedDate if you want to track when records were initially created) and have the trigger only modify the ModifiedDate column after updates.

See the documentation for more information on the inserted and deleted tables.

edited Mar 11 '15 at 14:23

answered Mar 11 '15 at 14:17

jpw

44,361
6
66
86

Also, I wouldn't use a trigger for Insert to set the value for the ModifiedDate column, but simply set it's default value to GETDATE(). – Zohar Peled Mar 11 '15 at 14:19
@ZoharPeled Yeah, that sounds like a good idea. @jpw Thank you, I'm thinking this will work on UPDATE only, if I decide to make `GETDATE()` the default value ? – Kalec Mar 11 '15 at 14:23
default values only work for Insert. you still need the trigger for update – Zohar Peled Mar 11 '15 at 14:24
@ZoharPeled Yes, I understand. I already made the modification I was talking about and tested it (it works), but I'll rephrase: If I modify the trigger for `UPDATE` only, and add a default `GETDATE()` for `ModifiedDate`, it should work similarly ? And it does, only better. Thanks again! – Kalec Mar 11 '15 at 14:35

score 3 · Answer 2 · answered Sep 25 '20 at 09:22

3

CREATE TRIGGER ModDate
    ON TableX
    FOR INSERT, UPDATE
AS
BEGIN
    UPDATE TableX 
    SET ModifiedDate = GETDATE()
    WHERE Id = (Select Id from Inserted)
END

answered Sep 25 '20 at 09:22

Md Shahriar

2,072
22
11

score 0 · Answer 3 · answered Mar 11 '15 at 15:45

If you don't have keys on the insert data and you are not in command of the sql to add a default on the modifieddate column, you can get the insert trigger where the modifieddate column is null:

CREATE TRIGGER ModDate
ON TableX
AFTER INSERT
AS
BEGIN
UPDATE tableX  SET ModifiedDate = GETDATE() where modifieddate is null
END

Trigger on insert and update that adds modification date

3 Answers3