65

It would be nice to know how to create a trigger that auto-updates the modifiedDate column in my SQL Server table:

Table TimeEntry

Id (PK)
UserId (FK)
Description
Time
GenDate
ModDate

Trigger code:

+   TR_TimeEntry_UpdateModDate()
+   TR_TimeEntry_InsertGenDate()

An example for update ModDate would be nice.

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Julian
  • 1,264
  • 2
  • 10
  • 18

1 Answers1

118

My approach:

  • define a default constraint on the ModDate column with a value of GETDATE() - this handles the INSERT case

  • have a AFTER UPDATE trigger to update the ModDate column

Something like:

CREATE TRIGGER trg_UpdateTimeEntry
ON dbo.TimeEntry
AFTER UPDATE
AS
    UPDATE dbo.TimeEntry
    SET ModDate = GETDATE()
    WHERE ID IN (SELECT DISTINCT ID FROM Inserted)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 7
    Also it's worth considering `getUtcDate()` depending on your timezone requirements. – Rory Feb 04 '13 at 18:20
  • 58
    And if anyone was wondering, like me, whether this will recursively call itself, there's a RECURSIVE_TRIGGERS database option that you'd normally have off which prevents a trigger like this from calling itself. http://msdn.microsoft.com/en-us/library/ms190739.aspx – Rory Feb 04 '13 at 18:23
  • 2
    I didn't get it. I should do `SET RECURSIVE_TRIGGERS` to `ON` or `OFF` to prevent this? – MarceloBarbosa Jan 12 '16 at 14:54
  • 1
    @MarceloBarbosa - I know it's late but you want it OFF. Otherwise the trigger call itself after the update it just executed. – Grambot Aug 16 '16 at 18:38
  • 9
    Where does the variable `Inserted` come from? – 4ndro1d Sep 05 '16 at 11:46
  • 18
    @4ndro1d: `Inserted` and `Deleted` are two pseudo tables that always exist inside each trigger. They represented the newly inserted or the deleted rows that caused the trigger to fire - and in the case of an `UPDATE`, `Inserted` contains the new value *after* the `UPDATE` operation, while `Deleted` has the old values *before* the update – marc_s Sep 05 '16 at 12:14
  • For those who have used this in a production environment. Do you notice any performance degradation? I'm weighing this option against the script-based approach for a system I'm currently designing. – pim Jul 22 '17 at 20:34
  • Updating modified date via code is it faster then updating it via the trigger? – Raj Saraogi Aug 27 '19 at 11:10
  • 2
    Why do you have to do a DISTINCT? – Ε Г И І И О Sep 27 '19 at 08:55
  • @Mideus: if you have a different answer - please post it as such - don't just "hijack" my response and totally change its intent ! – marc_s Nov 07 '19 at 17:57
  • Run `SELECT name, is_recursive_triggers_on FROM sys.databases` to check if recursive triggers are enabled – Paolo Fulgoni Feb 11 '21 at 11:56
  • @ΕГИІИО it is not required – Rym Nov 23 '21 at 13:24