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