Generating Archive Tables and Triggers

I create archive tables to log all of the changes made to a table. The archive tables look like the base table with a few columns added. Then the base tables need a trigger to insert the old row right before a change. I used the stored proc below to generate the archive tables and triggers. You need a schema called “archives” for this to work.

Copy Text

create proc CreateArchive(
@tablename nvarchar(200), 
@schema nvarchar(200) = 'dbo') 
as 
begin 
declare @sql nvarchar(4000) 
set @sql = ' create table archives.'+
    @tablename+'Arc ([archiveId] [int] IDENTITY(1,1) NOT NULL, [archiveAction] [char](1) NOT NULL, [archiveBy] [varchar](128) NULL, [archived] [datetime] NULL, ' 
    
    select @sql = @sql + '['+c.COLUMN_NAME+'] ' + c.DATA_TYPE + 
        CASE WHEN c.CHARACTER_MAXIMUM_LENGTH > 0 
            THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')' 
            WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 
            THEN '(MAX)' ELSE '' END + ' NULL, ' 
    from INFORMATION_SCHEMA.COLUMNS c 
    where TABLE_NAME = @tablename 
        and TABLE_SCHEMA = @schema 
    order by c.ORDINAL_POSITION 
    
    set @sql = @sql + 'CONSTRAINT [PK_'+ @tablename +
    'Arc] PRIMARY KEY CLUSTERED ( [archiveId] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]; ALTER TABLE [archives].[' 
    + @tablename + 
    'Arc] ADD CONSTRAINT [DF_' + 
    @tablename + 
    'Arc_archiveBy] DEFAULT (user_name()) FOR [archiveBy]; ALTER TABLE [archives].[' +
    @tablename + 'Arc] ADD CONSTRAINT [DF_' + @tablename + 
    'Arc_archived] DEFAULT (getdate()) FOR [archived]; grant all on [archives].[' +
    @tablename + 'Arc] to public;' 
    
    print @sql 
    
    exec sp_executesql @sql 
    
    set @sql = ' Create trigger [' + @schema + '].[t' + @tablename + 'Arc] on [' + @schema + '].[' + @tablename + 
    '] for update, delete as begin set nocount on declare @Action char(1) if exists (select 1 from inserted) set @Action = ''U'' else set @Action = ''D'' insert into archives.[' + 
    @tablename + 'Arc] (archiveAction' 
    
    select @sql = @sql + ', ['+c.COLUMN_NAME+']' 
    from INFORMATION_SCHEMA.COLUMNS c 
    where TABLE_NAME = @tablename 
    order by c.ORDINAL_POSITION 
    
    set @sql = @sql + ') select @Action' select @sql = @sql + ', ['+c.COLUMN_NAME+']' 
    from INFORMATION_SCHEMA.COLUMNS 
    c where TABLE_NAME = @tablename 
    order by c.ORDINAL_POSITION 
    
    set @sql = @sql + ' from deleted end;' 
    print @sql 
    exec sp_executesql @sql 
end 
go

Here are some of the scripts I used to help with this:

Copy Text

-- create the archives schema 
create schema archives 

-- Generate the commands that call the stored proc for each table: 
select 'exec CreateArchive '+TABLE_NAME + ';' 
from INFORMATION_SCHEMA.TABLES 
where TABLE_SCHEMA = 'dbo' 
order by TABLE_NAME 

-- drop the tables if I got an error: 
select 'drop table archives.'+TABLE_NAME + ';' 
from INFORMATION_SCHEMA.TABLES 
where TABLE_SCHEMA = 'archives' 
order by TABLE_NAME 

-- drop the triggers if I got an error: 

select 'drop trigger '+name + ';' 
from sys.triggers 
where create_date > getdate() - 1 
order by name 

-- see what object were last changed in the database 
select * 
from sys.objects 
order by modify_date desc

Related Posts

Leave a Comment Cancel Reply