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
  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
    fetch next from c into @s
    if @@fetch_status != 0 break
    select @result = case when @result is null then '' else @result + ',' end + @s
  close c
  deallocate c

  return @result

CREATE view perfect.Columns as

select O.object_id as Table_id, C.Column_name as Name,
  Data_type + 
    when Data_type IN ('char','nchar','varchar','nvarchar','binary','varbinary')
      case when Character_maximum_length > 0 
      then '(' + convert(varchar,Character_maximum_length) + ')'
      else '(max)' 
    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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.