Jon's Blog

.NET Development & More

SQL Server Trigger for Row Archive

This trigger will write to an archive table when a row is updated or deleted.  Very useful when you want to know who did what and when.

USE [DatabaseName]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trigTableName_Archive] 
   ON  [dbo].[TableName]
   FOR UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO TableName_ARCHIVE
        (Column1,
        Column2,
        Column3)
    SELECT
        Column1,
        Column2,
        Column3
    FROM deleted

END