I have written a SQL server stored procedure to generate "log" tables which store the old values of rows when a table is updated, and also record when rows are deleted ( and by who ). It's quite straight-forward, the log table has identical rows to the table being logged, plus a "Changed" column which records the date the data was changed. A trigger is used to copy the old value of the row to the log table.
LogTable is the main procedure, function GetColumns computes a list of column names ( optionally with type declaration ), and Columns is a view that computes the column type declaration from system tables, Objects is a view of all database objects. The comment at the top of LogTable is to remind me to set up the "UpdatedBy" column in the table being logged before enabling logging - this is not required, but allows tracking of who did the update ( or created the row in the first place ).
Handler is a table which generates stored procedures which handle http requests, setting the flag LogDelete enables the generation code which logs the deletion of a row.
I don't have any specific questions for review. I am aware of the integrated feature for tracking changes, but decided it was simpler and more transparent doing it this way, with changes easily visible in the log tables, and this approach is not dependent on the version of SQL server being used.
CREATE procedure perfect.[LogTable] @table int as /* To log which user made changes, before setting up logging for a table, add a field UpdatedBy int to the table to be logged. This should be set as an Auto field in the Add and Update handlers, set to @cu. */ declare @sql nvarchar(max), @table_schema nvarchar(128), @table_name nvarchar(128), @tname nvarchar(max), @lname nvarchar(max) select @table_schema = Sch, @table_name = Name from perfect.Objects where id = @table set @tname = quotename( @table_schema) + '.' + quotename(@table_name ) -- full name of table being logged set @lname = 'log.' + quotename( @table_schema + '_' + @table_name ) -- full name of log table -- Create log table that stores deleted row when a table if updated or deleted. select @sql = 'create table ' + @lname + '(Changed datetime,' + perfect.GetColumns( @table, 1 ) + ')' execute( @sql ) -- Create a clustered index for the log table. select @sql = 'create clustered index ' + quotename('CI_' + @table_schema + @table_name ) +' on ' + @lname + '(id,Changed)' execute( @sql ) -- Create trigger that saves the deleted rows when the table is updated or deleted. select @sql = 'create trigger ' + quotename(@table_schema) + '.' + quotename(@table_name + 'logtrigger' ) + ' on ' + @tname + ' for update, delete as insert into ' + @lname + '(Changed,' + perfect.GetColumns( @table, 0 ) + ') select getdate(),'+ perfect.GetColumns( @table, 0 ) + ' from deleted' execute( @sql ) -- Create table for logging deletes. select @sql = 'create table log.' + quotename(@table_schema + '_Del_' + @table_name) + '(id int,Deleted datetime,DeletedBy int)' execute( @sql ) -- Enable logging of deletes in Edit handlers. update perfect.Handler set LogDelete = 1 where Sch = @table_schema and TableName = @table_name and Kind = 4 CREATE function perfect.[GetColumns] ( @table int, @option int ) returns nvarchar(max) as begin declare @result nvarchar(max) declare c cursor local for select quotename(Name) + case when @option = 1 then ' ' + Type + Nullable else '' end from perfect.Columns where Table_id = @table order by Name declare @s nvarchar(200) open c while 1 = 1 begin fetch next from c into @s if @@fetch_status != 0 break select @result = case when @result is null then '' else @result + ',' end + @s end close c deallocate c return @result end CREATE view perfect.Columns as select O.object_id as Table_id, C.Column_name as Name, Data_type + case when Data_type IN ('char','nchar','varchar','nvarchar','binary','varbinary') then case when Character_maximum_length > 0 then '(' + convert(varchar,Character_maximum_length) + ')' else '(max)' end when Data_type IN ('decimal','numeric') then '(' + convert(varchar,Numeric_precision) + ',' + convert(varchar,Numeric_scale) + ')' else '' end as Type, case when Is_nullable='no' then ' not null' else '' end as Nullable from sys.all_objects as O inner join sys.schemas as S on S.Schema_id = O.Schema_id inner join information_schema.Columns as C on C.Table_schema = S.Name and C.Table_name = O.Name where O.Type in ( 'U','V' ) -- Tables and Views CREATE view [perfect].[Objects] as select O.object_id as id, S.Name as Sch, O.Name, O.Type, convert(nvarchar(max),P.Value) as Description, O.schema_id from sys.all_objects as O inner join sys.schemas as S on S.schema_id = O.schema_id left join sys.extended_properties as P on P.major_id = O.object_id and P.Name = 'Description' where O.is_ms_shipped = 0