Sometimes it is useful to keep track of a modification date or modification user in a field, especially in a master data environment. You can use a trigger on a table for this. It must work both when new data is inserted and when data is updated.
CREATE TRIGGER [dbo].[tblTrigger_AfterUpdate] ON [dbo].[REF_KlantNorm]AFTER UPDATE, INSERTAS
UPDATE KNSET MetaMutationDate = GETDATE(), MetaMutationUser = SYSTEM_USERFROM dbo.REF_KlantBudget AS KN, INSERTED AS iWHERE i.DebiteurenMapCode = KN.DebiteurenMapCodeAND i.BranchCode = KN.BranchCodeAND i.Jaar = KN.JaarThis trigger updates the date/user of the customers that were updated (the ones in the Inserted/Updated table). Make sure the join is done on the key.
Frequently asked questions
How do I keep track of a modification date and modification user in SQL Server?
Create a trigger on the table that updates MetaMutationDate and MetaMutationUser with GETDATE() and SYSTEM_USER. This is especially useful in a master data environment.
How do I make a trigger fire on both INSERT and UPDATE?
Put AFTER UPDATE, INSERT in the CREATE TRIGGER line. Then the trigger fires both for new data and for updated data.
How does an AFTER UPDATE trigger know which rows changed?
The trigger reads the INSERTED table. It holds the new or updated rows. You join INSERTED to the table you want to update on the key columns.
Why must the join in a trigger be on the key?
That way only the customers that are in INSERTED get a new date and user. Make sure the join is done on the key.
Which function do I use for the user name in a trigger in SQL Server?
SYSTEM_USER. In the example trigger this function fills the field MetaMutationUser. The date comes from GETDATE().
Comments
Related blogs
Calculate the nearest working day
Calculate time in hours and minutes between two dates
Dutch public holidays in SQL Server