mardi 12 juillet 2016

How to make a generic trigger to set the audit fields?

I want to automatically set the audit fields (UpdatedBy/UpdatedOn) on my tables. To that end, I have a trigger on every table that looks like this:

CREATE TRIGGER [dbo].[tr_AsyncMessage_Upd] ON [dbo].[AsyncMessage] AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE m
    SET 
       m.UpdatedOn = CURRENT_TIMESTAMP 
      ,m.UpdatedBy = SUSER_SNAME()
    FROM dbo.AsyncMessage m
    INNER JOIN inserted AS i 
    ON m.AsyncMessageID = i.AsyncMessageID

END

However, I'd rather not have to write a trigger for every single table. Is there a way to tell SQL Server to auto set them for me? Or is there a way to have a single trigger to cover all tables?

Aucun commentaire:

Enregistrer un commentaire