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.
This question is related to
sql-server
sql-server-2008
date
triggers
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)
Source: Stackoverflow.com