I implement this trigger to automatically update the CreatedOn, CreatedBy, ModifiedOn, ModifiedBy fields on almost all tables that I create in SQL Server so I decided it was time to share.
Even if you don’t think you need this trigger, I can assure you that you will be glad you added them at some point in the future. It is just so useful to know who created or modified the rows when data quality issues arise.
This trigger is not a full audit trail, it only retains information on the latest change. A full audit trail would require at least one dedicated audit table and is a much more involved, whereas this is simple to implement.
It assumes a unique primary key on each table and works for both SQL user accounts and integrated security.
This example was taken from a SQL Server 2005 database. I have successfully used the same trigger in SQL Server 2008.
Hope this helps.
Joe Kunk
Microsoft MVP VB
Okemos, MI USA
Create TRIGGER [trgMyTableCreatedModified]
ON dbo.MyTable
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
Declare @now datetime
Declare @user varchar(50)
SELECT
@now = GetDate()
,@user = user_name()
UPDATE T SET
CreatedOn = COALESCE(I.CreatedOn, @now)
,CreatedBy = COALESCE(I.CreatedBy,@user)
,ModifiedOn = @now
,ModifiedBy = @user
FROM inserted I
JOIN dbo.MyTable as T
ON T.MyTableKey = I.MyTableKey
END
