January 29, 2013 at 3:17 am
Hi,
I have to write a trigger on my table to prevent data delete and also to log what is tried to delete.
I wrote following code, but it is not working for both requirements. If I use Commit Transaction, it allow delete and log deleted data in log table. if I use Rollbak, it rollbacks and don't write data in log table. but I want to prevent delete on table and also log data that is tried to delete.
How can I achieve both objectives ?
CREATE TRIGGER trg_tblDomain_DeleteLog
ON tblDomain
AFTER DELETE
AS
BEGIN
INSERT INTO tblDomain_tmpTrg
SELECT * ,GETDATE()
FROM DELETED;
COMMIT TRANSACTION ;
RAISERROR ('rows deleted from tblDomain .', 16, 1);
RETURN
END;
January 29, 2013 at 3:46 am
That is the case where table variable will be quite handy as it can keep data in rollback!
Here I've setup small sample for you. Hope you can follow the idea...
create table test(id int, val varchar(10))
GO
create table log_test(userName nvarchar(255), actiondate datetime, id int, val varchar(10))
go
INSERT test SELECT 1, 'a'
INSERT test SELECT 2, 'b'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.tr_test_D
ON dbo.test
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
-- use table variable to store data to be logged before rolling back transaction
declare @t table ( id int, val varchar)
insert @t select id, val from deleted
ROLLBACK
-- transacion rolled-back, but data is still available in @t
-- log it into proper table
insert log_test
select suser_sname(), getutcdate(), id, val
from @t
-- re-raise the error to make it clear!
raiserror ('Delete from this table is not allowed, your action is logged!',16,1)
END
GO
delete dbo.test
go
select * from dbo.log_test
go
January 29, 2013 at 5:06 am
thank you SSCrazy
it worked 🙂
January 29, 2013 at 8:45 am
Or you could use and INSTEAD OF trigger and not have to worry about the transaction.
CREATE TRIGGER dbo.tr_test_D
ON dbo.test
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
insert log_test
select suser_sname(), getutcdate(), id, val
from deleted
-- re-raise the error to make it clear!
raiserror ('Delete from this table is not allowed, your action is logged!',16,1)
END
GO
delete dbo.test
go
select * from dbo.log_test
go
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2013 at 9:14 am
Sean Lange (1/29/2013)
Or you could use and INSTEAD OF trigger and not have to worry about the transaction....
I do agree - it looks cleaner and easier!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply