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 ?

Answer 1

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.

Answer 2

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