Tuesday, January 29, 2013

MSSQL onDelete trigger RollBack transaction and Log deleted data

I have a scenario to rollBack any delete on table and also log whatever was delete candidate

-- Original table
CREATE TABLE dbo.tblDomain(
    DoaminId INT NULL,
    Description varchar(50) NULL
     );
-- Table Population
insert into dbo.tblDomain   
select 1,'Desc1'
union All
select 2,'Desc2'
union All
select 3,'Desc3'
union All
select 4,'Desc4';

/*---============= Creating table to store deleted records.
table structure is same as dbo.tblDomain except last 2 columns
FromDeleteUser varchar (100) –- who initiated delete
DeleteDate datetime          -- datatime of delete transaction

*/
CREATE TABLE dbo.tblDomain_tmpTrg(
    DoaminId INT NULL,
    Description varchar(500) NULL,
    FromDeleteUser varchar (100),
    DeleteDate datetime.
    FromDeleteUser varchar (100),
    DeleteDate datetime
    );
  
--===== Create trigger to fire on primary table delete
CREATE TRIGGER trg_tblDomain_DeleteLog
   ON  dbo.tblDomain
   AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- use table variable to store data to be logged before rolling back transaction
    declare @trg table (      DoaminId INT NULL,
                            Description varchar(500) NULL,
                            FromDeleteUser varchar (100),
                            DeleteDate datetime
                    )
    insert @trg select * from deleted
  
    ROLLBACK
    -- transacion rolled-back, but data is still available in @trg
    -- log it into proper table
    insert dbo.tblDomain_tmpTrg
    select * , suser_sname(), getutcdate()
    from @trg
    -- re-raise the error to make it clear!
    raiserror ('Delete from table dbo.dbo.tblDomain is not allowed, your action is logged!',16,1)
  
END
GO

--===================== Verification
--SELECT * FROM dbo.tblDomain;
--DELETE FROM dbo.tblDomain ;
--SELECT  * FROM  dbo.tblDomain_tmpTrg;
--=== Drop objects
--DROP TRIGGER trg_tblDomain_DeleteLog;
--DROP dbo.tblDomain_tmpTrg;

No comments:

Post a Comment