My approach:
-
define a default constraint on the
ModDate
column with a value ofGETDATE()
– this handles theINSERT
case -
have a
AFTER UPDATE
trigger to update theModDate
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)