Develop undo feature with trigger

There are 4 types actions in database operation, called CRUD – Create, Read, Update, Delete. The data would be permanently changed once a transaction committed. How do we recover unwanted changes that was made by mistake on database? With database trigger we can easily solve this problem.

TYPE OF TRIGGERS

• AFTER INSERT Trigger
• AFTER UPDATE Trigger
• AFTER DELETE Trigger
• INSTEAD OF INSERT Trigger
• INSTEAD OF UPDATE Trigger
• INSTEAD OF DELETE Trigger

We will use AFTER triggers to UNDO the changes.

KEY POINTS

• In order to log the user who made changes to the data, we need to get user identity from the application. There are many options to pass user information to trigger.
• add a column to store user id on each table, but when perform delete action the record would not be there;
• create a separate table to store user id on each action, this looks ugly;
• passing user id to database then trigger can get this user id. That is context_info.

As we know, the build-in function context_info can carry some information on session bases. Create a store procedure to allow application to pass user id to database and keep it in the session:

create proc dbo.USP_HIS_ContextInfo @empId varchar(110) as
begin
 set nocount on
 declare @info binary(128)
 set @info = cast(@empId as binary(128))
 set context_info @info
end
go

In undo stored procedure we can catch it like this:

declare @info varchar(255)
set @info = replace(cast(context_info() as varchar(255)),0x0,'')

• In order to avoid large data would be stored in the log, we only log the data that has been changed, and log each action in one record in log table called tbl_cellCodeChangeLog. The changed data is stored in one field with pine-line delimiter. We can find the original values and new values from the deleted and inserted virtual tables.
• Actually we only need to log the original values and the new values are always in the data table. Recovering the data we just need to restore the original values to the data table.

Log table definition:

CREATE TABLE dbo.tbl_cellCodeChangeLog(
 changeId int IDENTITY(1,1) PRIMARY KEY,
 recordID int NOT NULL,
 changeAction varchar(6) NOT NULL,
 tableName varchar(255) NOT NULL,
 columnNames varchar(max),
 oldValues varchar(max),
 changedByEmpNo varchar(255),
 changeTimestamp datetime DEFAULT (getdate())
)
GO

• In order to avoid firing trigger when performing undo actions, we need to send a signal to trigger. Passing the table name to context_info, the trigger will do nothing if the context_info is the table name.
• Getting changed values using the following logic:

select 'sCellCode',
 case when d.sCellCode is null then 'null' else cast(d.sCellCode as varchar) end
from deleted d
 join inserted i on i.recordid=d.recordid
where i.sCellCode!=d.sCellCode

TRIGGERS FOR INSERT, UPDATE, DELETE

--INSERT
CREATE trigger [dbo].[trg_insert_TBL_E2DIALER_MSI_CellCodes] on [dbo].[TBL_E2DIALER_MSI_CellCodes]
after INSERT as
begin
 set nocount on
 
 declare @info binary(128), @sinfo varchar(255), @empId varchar(50), @tablename sysname, @recordId int
 select @info = context_info(),@sinfo=convert(varchar(255),@info)
 select @empId=left(@sinfo,case when charindex('|',@sinfo)>0 then charindex('|',@sinfo)-1 else len(@sinfo) end), @tablename=right(@sinfo, len(@sinfo)-charindex('|',@sinfo))
 if @empId is null set @empId = SYSTEM_USER

 if @tablename='[dbo].[TBL_E2DIALER_MSI_CellCodes]' return
 
 declare csr cursor local for
 select recordId from inserted
 open csr 
 fetch next from csr into @recordId
 while @@FETCH_STATUS=0
 begin
  insert into dbo.tbl_cellCodeChangeLog(
   [changeAction],
   [tableName],
   [changedByEmpNo],
   [recordID])
  values ('INSERT',
   '[dbo].[TBL_E2DIALER_MSI_CellCodes]',
   @empId,
   @recordId)
 
 fetch next from csr into @recordId
 end
 close csr
 deallocate csr

 set context_info 0x
end

--DELETE
CREATE trigger [dbo].[trg_delete_TBL_E2DIALER_MSI_CellCodes] on [dbo].[TBL_E2DIALER_MSI_CellCodes]
after DELETE as
begin
 SET NOCOUNT ON
 declare @info binary(128), @sinfo varchar(255), @empId varchar(50), @tablename sysname, @recordId int
 select @info = context_info(),@sinfo=convert(varchar(255),@info)
 select @empId=left(@sinfo,case when charindex('|',@sinfo)>0 then charindex('|',@sinfo)-1 else len(@sinfo) end), @tablename=right(@sinfo, len(@sinfo)-charindex('|',@sinfo))
 if coalesce(@empId,'')='' set @empId = SYSTEM_USER

 if @tablename='[dbo].[TBL_E2DIALER_MSI_CellCodes]' return
 
 declare csr cursor local for
 select recordId from deleted
 open csr 
 fetch next from csr into @recordId
 while @@FETCH_STATUS=0
 begin 
  create table #t(clm sysname, v varchar(max)) 
  insert into #t select 'sCellCode',case when d.sCellCode is null then 'null' else cast(d.sCellCode as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'sCellCode_Description',case when d.sCellCode_Description is null then 'null' else cast(d.sCellCode_Description as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'sCellCode_Group',case when d.sCellCode_Group is null then 'null' else cast(d.sCellCode_Group as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'dStartDate',case when d.dStartDate is null then 'null' else cast(d.dStartDate as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'dEndDate',case when d.dEndDate is null then 'null' else cast(d.dEndDate as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'sSite',case when d.sSite is null then 'null' else cast(d.sSite as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsDMC',case when d.bIsDMC is null then 'null' else cast(d.bIsDMC as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsUpsell',case when d.bIsUpsell is null then 'null' else cast(d.bIsUpsell as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsLoyalty',case when d.bIsLoyalty is null then 'null' else cast(d.bIsLoyalty as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsMobility',case when d.bIsMobility is null then 'null' else cast(d.bIsMobility as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsDaily',case when d.bIsDaily is null then 'null' else cast(d.bIsDaily as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsWeekly',case when d.bIsWeekly is null then 'null' else cast(d.bIsWeekly as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsMonthly',case when d.bIsMonthly is null then 'null' else cast(d.bIsMonthly as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsQuartely',case when d.bIsQuartely is null then 'null' else cast(d.bIsQuartely as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsBellCanada',case when d.bIsBellCanada is null then 'null' else cast(d.bIsBellCanada as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'bIsBellAliant',case when d.bIsBellAliant is null then 'null' else cast(d.bIsBellAliant as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'sRelationship',case when d.sRelationship is null then 'null' else cast(d.sRelationship as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'sFootprint',case when d.sFootprint is null then 'null' else cast(d.sFootprint as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'sProduct_Holding',case when d.sProduct_Holding is null then 'null' else cast(d.sProduct_Holding as varchar) end from deleted d where recordId=@recordId
  insert into #t select 'recordID',case when d.recordID is null then 'null' else cast(d.recordID as varchar) end from deleted d where recordId=@recordId 

  insert into dbo.tbl_cellCodeChangeLog(
   changeAction,
   tableName,
   columnNames,
   oldValues,
   changedByEmpNo,
   recordID)
  values ('delete',
   '[dbo].[TBL_E2DIALER_MSI_CellCodes]',
   STUFF((select '|'+clm from #t for xml path('')),1,1,''),
   STUFF((select '|'+v from #t for xml path('')),1,1,''),
   @empId,
   @recordId)
 
 fetch next from csr into @recordId
 end
 close csr
 deallocate csr
END
GO

--UPDATE
CREATE trigger [dbo].[trg_update_TBL_E2DIALER_MSI_CellCodes] on [dbo].[TBL_E2DIALER_MSI_CellCodes]
after UPDATE as
begin
 set nocount on
 declare @info binary(128), @sinfo varchar(255), @empId varchar(50), @tablename sysname, @recordId int
 select @info = context_info(),@sinfo=convert(varchar(255),@info)
 select @empId=left(@sinfo,case when charindex('|',@sinfo)>0 then charindex('|',@sinfo)-1 else len(@sinfo) end), @tablename=right(@sinfo, len(@sinfo)-charindex('|',@sinfo))
 if coalesce(@empId,'')='' set @empId = SYSTEM_USER

 if @tablename='[dbo].[TBL_E2DIALER_MSI_CellCodes]' return
 
 declare csr cursor local for
 select recordId from deleted
 open csr 
 fetch next from csr into @recordId
 while @@FETCH_STATUS=0
 begin
  create table #t(clm sysname, v varchar(max))
  insert into #t select 'sCellCode',case when d.sCellCode is null then 'null' else cast(d.sCellCode as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.sCellCode!=d.sCellCode) or (i.sCellCode is null and d.sCellCode is not null) or (i.sCellCode is not null and d.sCellCode is null);
  insert into #t select 'sCellCode_Description',case when d.sCellCode_Description is null then 'null' else cast(d.sCellCode_Description as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.sCellCode_Description!=d.sCellCode_Description) or (i.sCellCode_Description is null and d.sCellCode_Description is not null) or (i.sCellCode_Description is not null and d.sCellCode_Description is null);
  insert into #t select 'sCellCode_Group',case when d.sCellCode_Group is null then 'null' else cast(d.sCellCode_Group as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.sCellCode_Group!=d.sCellCode_Group) or (i.sCellCode_Group is null and d.sCellCode_Group is not null) or (i.sCellCode_Group is not null and d.sCellCode_Group is null);
  insert into #t select 'dStartDate',case when d.dStartDate is null then 'null' else cast(d.dStartDate as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.dStartDate!=d.dStartDate) or (i.dStartDate is null and d.dStartDate is not null) or (i.dStartDate is not null and d.dStartDate is null);
  insert into #t select 'dEndDate',case when d.dEndDate is null then 'null' else cast(d.dEndDate as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.dEndDate!=d.dEndDate) or (i.dEndDate is null and d.dEndDate is not null) or (i.dEndDate is not null and d.dEndDate is null);
  insert into #t select 'sSite',case when d.sSite is null then 'null' else cast(d.sSite as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.sSite!=d.sSite) or (i.sSite is null and d.sSite is not null) or (i.sSite is not null and d.sSite is null);
  insert into #t select 'bIsDMC',case when d.bIsDMC is null then 'null' else cast(d.bIsDMC as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsDMC!=d.bIsDMC) or (i.bIsDMC is null and d.bIsDMC is not null) or (i.bIsDMC is not null and d.bIsDMC is null);
  insert into #t select 'bIsUpsell',case when d.bIsUpsell is null then 'null' else cast(d.bIsUpsell as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsUpsell!=d.bIsUpsell) or (i.bIsUpsell is null and d.bIsUpsell is not null) or (i.bIsUpsell is not null and d.bIsUpsell is null);
  insert into #t select 'bIsLoyalty',case when d.bIsLoyalty is null then 'null' else cast(d.bIsLoyalty as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsLoyalty!=d.bIsLoyalty) or (i.bIsLoyalty is null and d.bIsLoyalty is not null) or (i.bIsLoyalty is not null and d.bIsLoyalty is null);
  insert into #t select 'bIsMobility',case when d.bIsMobility is null then 'null' else cast(d.bIsMobility as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsMobility!=d.bIsMobility) or (i.bIsMobility is null and d.bIsMobility is not null) or (i.bIsMobility is not null and d.bIsMobility is null);
  insert into #t select 'bIsDaily',case when d.bIsDaily is null then 'null' else cast(d.bIsDaily as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsDaily!=d.bIsDaily) or (i.bIsDaily is null and d.bIsDaily is not null) or (i.bIsDaily is not null and d.bIsDaily is null);
  insert into #t select 'bIsWeekly',case when d.bIsWeekly is null then 'null' else cast(d.bIsWeekly as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsWeekly!=d.bIsWeekly) or (i.bIsWeekly is null and d.bIsWeekly is not null) or (i.bIsWeekly is not null and d.bIsWeekly is null);
  insert into #t select 'bIsMonthly',case when d.bIsMonthly is null then 'null' else cast(d.bIsMonthly as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsMonthly!=d.bIsMonthly) or (i.bIsMonthly is null and d.bIsMonthly is not null) or (i.bIsMonthly is not null and d.bIsMonthly is null);
  insert into #t select 'bIsQuartely',case when d.bIsQuartely is null then 'null' else cast(d.bIsQuartely as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsQuartely!=d.bIsQuartely) or (i.bIsQuartely is null and d.bIsQuartely is not null) or (i.bIsQuartely is not null and d.bIsQuartely is null);
  insert into #t select 'bIsBellCanada',case when d.bIsBellCanada is null then 'null' else cast(d.bIsBellCanada as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsBellCanada!=d.bIsBellCanada) or (i.bIsBellCanada is null and d.bIsBellCanada is not null) or (i.bIsBellCanada is not null and d.bIsBellCanada is null);
  insert into #t select 'bIsBellAliant',case when d.bIsBellAliant is null then 'null' else cast(d.bIsBellAliant as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.bIsBellAliant!=d.bIsBellAliant) or (i.bIsBellAliant is null and d.bIsBellAliant is not null) or (i.bIsBellAliant is not null and d.bIsBellAliant is null);
  insert into #t select 'sRelationship',case when d.sRelationship is null then 'null' else cast(d.sRelationship as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.sRelationship!=d.sRelationship) or (i.sRelationship is null and d.sRelationship is not null) or (i.sRelationship is not null and d.sRelationship is null);
  insert into #t select 'sFootprint',case when d.sFootprint is null then 'null' else cast(d.sFootprint as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.sFootprint!=d.sFootprint) or (i.sFootprint is null and d.sFootprint is not null) or (i.sFootprint is not null and d.sFootprint is null);
  insert into #t select 'sProduct_Holding',case when d.sProduct_Holding is null then 'null' else cast(d.sProduct_Holding as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.sProduct_Holding!=d.sProduct_Holding) or (i.sProduct_Holding is null and d.sProduct_Holding is not null) or (i.sProduct_Holding is not null and d.sProduct_Holding is null);
  insert into #t select 'recordID',case when d.recordID is null then 'null' else cast(d.recordID as varchar) end from deleted d join inserted i on i.recordid=d.recordid where (i.recordID!=d.recordID) or (i.recordID is null and d.recordID is not null) or (i.recordID is not null and d.recordID is null)

  insert into dbo.tbl_cellCodeChangeLog(
   [changeAction],
   [tableName],
   [columnNames],
   [oldValues],
   [changedByEmpNo],
   [recordID])
  values ('update',
   '[dbo].[TBL_E2DIALER_MSI_CellCodes]',
   STUFF((select '|'+clm from #t for xml path('')),1,1,''),
   STUFF((select '|'+v from #t for xml path('')),1,1,''),
   @empId,
   @recordId)
 
 fetch next from csr into @recordId
 end
 close csr
 deallocate csr

 set context_info 0x
end
GO

UNDO PROC

CREATE proc [dbo].[USP_HIS_UNDO](@changeId int) as
set nocount on
declare @changeAction varchar(255), @tableName varchar(255), @recordID int
declare @columnNames varchar(max), @oldValues varchar(max), @hid varchar(255)=null
declare @sql varchar(max)

if not exists(select 1 from dbo.tbl_cellCodeChangeLog where changeId=@changeId)
begin
 RAISERROR('The changeId is not exist.', 1,1); --level,state
 return
end

select @changeAction=changeAction,@tableName=tableName,@recordID=recordID
from dbo.tbl_cellCodeChangeLog
where changeId=@changeId

declare @ctxinfo binary(128), @info varchar(1000)
set @info = coalesce(replace(cast(context_info() as varchar(255)),0x0,''),'')
if len(@info)>10
begin
 set context_info 0x0
 set @info = ''
end
set @info += '|'+@tableName
set @ctxinfo = cast(@info as binary(128))
set context_info @ctxinfo

if object_id('tempdb.dbo.#tbl_value') is not null drop table #tbl_value
CREATE TABLE #tbl_value (col varchar(max),val varchar(max))

begin tran
begin try
 --rollback one by one from the largest changeId
 declare csr1 cursor local for
 --get row histories
 select changeId,columnNames,oldValues from [dbo].tbl_cellCodeChangeLog where recordID=@recordID and changeId>=@changeId order by changeId desc
 open csr1 
 fetch next from csr1 into @changeId,@columnNames,@oldValues
 while @@FETCH_STATUS=0
 begin

  if @changeAction='update'
  begin
   if object_id('tempdb.dbo.#t1') is not null drop table #t1
   if object_id('tempdb.dbo.#t2') is not null drop table #t2

   --get original columns values pair
   select value, identity(int,1,1) id into #t1 from dbo.ufn_fnSplit(@columnNames,'|')
   select value, identity(int,1,1) id into #t2 from dbo.ufn_fnSplit(@oldValues,'|')
   insert into #tbl_value(col,val) select #t1.value,#t2.value from #t1 join #t2 on #t1.id=#t2.id
   --select * from #tbl_value
  
   declare @col varchar(max),@val varchar(max)
   declare csr2 cursor local for
   select * from #tbl_value
   open csr2 fetch next from csr2 into @col,@val
   while @@FETCH_STATUS=0
   begin
    set @sql='update '+@tableName+' set '+@col+'='''+@val+''' where recordID='+cast(@recordID as varchar)
    exec(@sql)
    fetch next from csr2 into @col,@val
   end
   close csr2
   deallocate csr2   
  end

  else if @changeAction='delete'
  begin
   set @sql='set identity_insert '+@tableName+' on'+
   '; insert into '+@tableName+' ('+replace(@columnNames,'|',',')+') values ('''+replace(replace(@oldValues,'|',''',''')+''')','''null''','null')+
   '; set identity_insert '+@tableName+' off;'
   exec(@sql)
  end

  else if @changeAction='insert'
  begin
   set @sql='delete from '+@tableName+' where recordId='+cast(@recordID as varchar)
   exec(@sql)
  end

  delete from dbo.tbl_cellCodeChangeLog where changeId=@changeId

  fetch next from csr1 into @changeId,@columnNames,@oldValues
 end
 close csr1
 deallocate csr1
commit tran
set context_info 0x
end try
begin catch
 rollback tran
 set context_info 0x
    DECLARE @Error_Message VARCHAR(1000);
    DECLARE @Error_Severity INT;
    DECLARE @Error_State INT;

    SELECT  @Error_Message = ERROR_MESSAGE() ,
            @Error_Severity = ERROR_SEVERITY() ,
            @Error_State = ERROR_STATE();

    RAISERROR(@Error_Message, @Error_Severity, @Error_State);
 return
end catch
GO

LAST WORDS

Using database trigger to perform undo function is simple and clear. No need to involve any fat framework to work with.

new Location: http://it.211cn.ca/2017/11/14/develop-undo-feature-with-trigger/