Generating Archive Tables and Triggers

Generating Archive Tables and Triggers

by jbrooks 18. September 2014 09:19

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 triggers 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.

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:

-- 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

Tags:

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

One Script

OneScript Continuous Integration for you database Scripts in version control are automatically combined into a release script.

RecentPosts