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

Question

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?

Answer 1

Try something like this. Copy the output of this, and check it over before you run it. This only works if the table has a primary key, and has the updatedby and updatedon columns.

SELECT 'CREATE TRIGGER tr_'+TABLE_NAME+'_Update ON '+TABLE_SCHEMA+'.'+TABLE_NAME+' 
        AFTER UPDATE
        AS
        BEGIN
            UPDATE T SET UPDATEDBY=SYSTEM_USER, UPDATEDON=CURRENT_TIMESTAMP
            FROM '+TABLE_SCHEMA+'.'+TABLE_NAME+' T
            JOIN inserted I ON T.'+COLUMN_NAME+'=I.'+COLUMN_NAME+'
        END'

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.COLUMN_NAME='UpdatedOn' AND CU.TABLE_NAME=C.TABLE_NAME)
AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C2 WHERE C2.COLUMN_NAME='UpdatedBy' AND CU.TABLE_NAME=C2.TABLE_NAME)