Develop undo feature with trigger

Develop undo feature with trigger

There are 4 types actions in database operation, called CRUD – Create, Read, Update, Delete. Basically there are only 3 actions would change the data, insert/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

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())
)
  • 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

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

UNDO PROC

CREATE proc [dbo].[USP_HIS_UNDO](@changeId int) as
begin
    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
  
       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
end

LAST WORDS

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

For Entity Framework solution read Develop UNDO feature with Entity Framework 6 and trigger

31 total views, 1 views today

Leave a Reply