Menu
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.
742
You can make the trigger in your create table statement with an with an on update . create table whatever ( ... updated datetime default current_timestamp on update current_timestamp ); And then there's MySQL's timestamp date type.
trigger_name: This is the name of the trigger which you want to create. table_name: This is the name of the table to which the trigger will be applied. SQL statements: SQL statements form the body of the trigger. These are a set of SQL operations that will be performed once the AFTER UPDATE trigger is invoked.
SQL Server has three types of triggers: DML (Data Manipulation Language) Triggers. DDL (Data Definition Language) Triggers. Logon 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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
