July 24, 2013 at 10:23 am
Hi,
I'm trying to write an INSTEAD OF DELETE trigger that also logs some data about the attempted delete. It doesn't have to be super-restrictive across the board such as trying to prevent deletes/truncate by sa or anything like that. We just want to have any routine delete operations stopped. Additionally, I would like to log as much as possible about when the delete attempt occurred and who attempted it.
My code is below. The trigger successfully stops the delete attempt, but when I check the log table, no records have been inserted. Could someone help me find out what is stopping the log record insert, and how I can fix it?
Thanks for any help,
webrunner
/****** Object: Trigger [dbo].[trNumber_D] Script Date: 07/24/2013 12:16:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trNumber_D] ON [dbo].[Numbers]
INSTEAD OF DELETE
AS
-- Log attempted delete info.
IF EXISTS ( SELECT 1 FROM deleted )
BEGIN
SET NOCOUNT ON;
INSERT INTO Number_Log ( ID, Number, Date_Inserted,
Date_Delete_Attempted, UserName )
SELECT d.ID, d.Number, d.Date_Inserted,
GETDATE(), SYSTEM_USER
FROM deleted d
-- Deletion from this table is not allowed.
RAISERROR('Deletion of records from Numbers is not allowed.', 16, 1)
RETURN;
END
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 24, 2013 at 10:36 am
RAISERROR cause transaction to rollback, so your log insert is rollbacked as well as delete...
Check use of self-linked server in order to simulate autonomous transaction in SQL Server.
July 24, 2013 at 10:53 am
Just off the top of my head you could write the audit data into a table variable, do the raiserror THEN do the insert into the base table. Table variables are not subject to rollbacks so the data isn't rolled back. I am wondering if the raiserror may halt execution, you may want to look at SET XACT_ABORT OFF and see what effect that might have.
CEWII
July 24, 2013 at 1:41 pm
Thanks for the advice.
It looks like switching the order of the code allowed me to write to the log table. See the code below. I also added a ROLLBACK statement.
When I run a delete statement on multiple rows via T-SQL, then the trigger properly logs the multiple rows that I tried to delete.
However, something different happens when I try to delete multiple rows from SSMS by opening the table and editing it in the GUI. In that case, only one row is logged. I think that is because the interface bails out after the failed delete of the first row. Does anyone know a way around that? Or am I just not coding the trigger correctly?
Thanks again,
webrunner
/****** Object: Trigger [dbo].[trNnumber_D] Script Date: 07/24/2013 12:04:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trNumber_D] ON [dbo].[Numbers]
INSTEAD OF DELETE
AS
-- Log attempted delete info.
IF EXISTS ( SELECT 1 FROM deleted )
BEGIN
SET NOCOUNT ON;
-- Deletion from this table is not allowed.
ROLLBACK
RAISERROR('Deletion of records from Numbers is not allowed.', 16, 1)
-- Insert info in to the log table.
INSERT INTO Number_Log ( AdmissionsID, Number, Date_Inserted,
Date_Delete_Attempted, UserName )
SELECT d.AdmissionsID, d.Number, d.date_inserted,
GETDATE(), SYSTEM_USER
FROM deleted d
RETURN;
END
GO
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 24, 2013 at 2:28 pm
Eugene Elutin (7/24/2013)
RAISERROR cause transaction to rollback, so your log insert is rollbacked as well as delete...
It's so untrue...
RAISERROR has nothing to do with the transaction.
It's just the way how SSMS works. It creates transaction and then rollbacks it if there are any errors.
And if you delete multiple rows then SSMS deletes them one by one, and as soon as it get's the first error it stop processing.
July 24, 2013 at 2:45 pm
Remove ROLLBACK from the trigger and lower severity of RAISERROR to 10, in this case SSMS won't show any errors, rows will disappear from the UI (but actually they won't be deleted) and the attempt will be logged.
July 24, 2013 at 3:01 pm
Alexander Suprun (7/24/2013)
Remove ROLLBACK from the trigger and lower severity of RAISERROR to 10, in this case SSMS won't show any errors, rows will disappear from the UI (but actually they won't be deleted) and the attempt will be logged.
Thanks!! That worked exactly the way I want it to.
So is the ROLLBACK statement really superfluous because it is an INSTEAD OF trigger?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 24, 2013 at 5:22 pm
webrunner (7/24/2013)
Alexander Suprun (7/24/2013)
Remove ROLLBACK from the trigger and lower severity of RAISERROR to 10, in this case SSMS won't show any errors, rows will disappear from the UI (but actually they won't be deleted) and the attempt will be logged.Thanks!! That worked exactly the way I want it to.
So is the ROLLBACK statement really superfluous because it is an INSTEAD OF trigger?
Thanks again,
webrunner
ROLLBACK in this case is not "superfluous", it's downright dangerous.
You've got to remember that there could be multiple SQL statements -- DELETE and/or INSERT and/or UPDATE -- in the transaction, not just the DELETE you want to ignore. By explicitly issuing a ROLLBACK, you force the entire transaction to be rolled back.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 25, 2013 at 7:36 am
Alexander Suprun (7/24/2013)
Eugene Elutin (7/24/2013)
RAISERROR cause transaction to rollback, so your log insert is rollbacked as well as delete...It's so untrue...
RAISERROR has nothing to do with the transaction.
It's just the way how SSMS works. It creates transaction and then rollbacks it if there are any errors.
And if you delete multiple rows then SSMS deletes them one by one, and as soon as it get's the first error it stop processing.
What do you mean by "RAISERROR has nothing to do with the transaction."
Read the original OP post. There is nothing there about SSMS.
RAISERROR in the trigger has everything to do with the transaction as it causes its rollback!
If the trigger wouldn't have one, then OP would see Log records created, no delete happened but also, no error returned to client.
And it doesn't matter what you are using to delete records.
Even if you would do it from any client using DELETE query and explicitly open a transaction, RAISERROR in this trigger would ROLLBACK any modifications happened before rollback.
SSMS (in Edit Top N Rows mode) performs all row modifications in a separate transactions eg. deletes.
Actually, if delete from this table is not permitted, I would explicitly rollback in the trigger, to ensure that the whole transaction is rollbacked.
Now, the most interesting thing for me here is the effect of using INSTEAD OF trigger.
If the OP would create ordinary trigger for delete and would raise an error and rollback before inserting into log, he would not see records in the log anyway, as rollback mark the whole transaction uncommittable. But, looks like in the INSTEAD OF trigger, whatever happens after rollback is committed.
July 25, 2013 at 8:01 am
ScottPletcher (7/24/2013)
webrunner (7/24/2013)
Alexander Suprun (7/24/2013)
Remove ROLLBACK from the trigger and lower severity of RAISERROR to 10, in this case SSMS won't show any errors, rows will disappear from the UI (but actually they won't be deleted) and the attempt will be logged.Thanks!! That worked exactly the way I want it to.
So is the ROLLBACK statement really superfluous because it is an INSTEAD OF trigger?
Thanks again,
webrunner
ROLLBACK in this case is not "superfluous", it's downright dangerous.
You've got to remember that there could be multiple SQL statements -- DELETE and/or INSERT and/or UPDATE -- in the transaction, not just the DELETE you want to ignore. By explicitly issuing a ROLLBACK, you force the entire transaction to be rolled back.
Ah, right. Thank you so much for this point about triggers. I will keep that in mind as I work with triggers.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 25, 2013 at 8:16 am
webrunner (7/25/2013)
ScottPletcher (7/24/2013)
webrunner (7/24/2013)
Alexander Suprun (7/24/2013)
Remove ROLLBACK from the trigger and lower severity of RAISERROR to 10, in this case SSMS won't show any errors, rows will disappear from the UI (but actually they won't be deleted) and the attempt will be logged.Thanks!! That worked exactly the way I want it to.
So is the ROLLBACK statement really superfluous because it is an INSTEAD OF trigger?
Thanks again,
webrunner
ROLLBACK in this case is not "superfluous", it's downright dangerous.
You've got to remember that there could be multiple SQL statements -- DELETE and/or INSERT and/or UPDATE -- in the transaction, not just the DELETE you want to ignore. By explicitly issuing a ROLLBACK, you force the entire transaction to be rolled back.
Ah, right. Thank you so much for this point about triggers. I will keep that in mind as I work with triggers.
Thanks again,
webrunner
Which point? 😉
July 25, 2013 at 8:20 am
Eugene Elutin (7/25/2013)
webrunner (7/25/2013)
ScottPletcher (7/24/2013)
webrunner (7/24/2013)
Alexander Suprun (7/24/2013)
Remove ROLLBACK from the trigger and lower severity of RAISERROR to 10, in this case SSMS won't show any errors, rows will disappear from the UI (but actually they won't be deleted) and the attempt will be logged.Thanks!! That worked exactly the way I want it to.
So is the ROLLBACK statement really superfluous because it is an INSTEAD OF trigger?
Thanks again,
webrunner
ROLLBACK in this case is not "superfluous", it's downright dangerous.
You've got to remember that there could be multiple SQL statements -- DELETE and/or INSERT and/or UPDATE -- in the transaction, not just the DELETE you want to ignore. By explicitly issuing a ROLLBACK, you force the entire transaction to be rolled back.
Ah, right. Thank you so much for this point about triggers. I will keep that in mind as I work with triggers.
Thanks again,
webrunner
Which point? 😉
The point Scott mentioned that ROLLBACK would undo the whole transaction in a trigger. I'm sure that behavior may be called for in certain circumstances but I thanked Scott for reminding me that this will be the result.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 25, 2013 at 8:27 am
Eugene Elutin (7/25/2013)
Alexander Suprun (7/24/2013)
Eugene Elutin (7/24/2013)
RAISERROR cause transaction to rollback, so your log insert is rollbacked as well as delete...It's so untrue...
RAISERROR has nothing to do with the transaction.
It's just the way how SSMS works. It creates transaction and then rollbacks it if there are any errors.
And if you delete multiple rows then SSMS deletes them one by one, and as soon as it get's the first error it stop processing.
What do you mean by "RAISERROR has nothing to do with the transaction."
Read the original OP post. There is nothing there about SSMS.
RAISERROR in the trigger has everything to do with the transaction as it causes its rollback!
If the trigger wouldn't have one, then OP would see Log records created, no delete happened but also, no error returned to client.
And it doesn't matter what you are using to delete records.
Even if you would do it from any client using DELETE query and explicitly open a transaction, RAISERROR in this trigger would ROLLBACK any modifications happened before rollback.
SSMS (in Edit Top N Rows mode) performs all row modifications in a separate transactions eg. deletes.
Actually, if delete from this table is not permitted, I would explicitly rollback in the trigger, to ensure that the whole transaction is rollbacked.
Now, the most interesting thing for me here is the effect of using INSTEAD OF trigger.
If the OP would create ordinary trigger for delete and would raise an error and rollback before inserting into log, he would not see records in the log anyway, as rollback mark the whole transaction uncommittable. But, looks like in the INSTEAD OF trigger, whatever happens after rollback is committed.
This is just false:
"RAISERROR causes ... rollback ... RAISERROR in this trigger would ROLLBACK ..."
A RAISERROR (with a level below 20) does not cause or force a ROLLBACK.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 25, 2013 at 8:29 am
Easy enough to demonstrate that:
USE tempdb --you can use a "real" db if you prefer
GO
IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'table1' AND schema_id = 1)
DROP TABLE dbo.table1
GO
CREATE TABLE dbo.table1 ( column1 int )
GO
CREATE TRIGGER table1__tr_ins
ON dbo.table1
AFTER INSERT
AS
SET NOCOUNT ON
RAISERROR('Inserting rows into table1 is not allowed.', 18, 1) WITH LOG
GO
INSERT INTO dbo.table1 VALUES(1)
GO
SELECT *
FROM dbo.table1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 25, 2013 at 8:44 am
webrunner (7/25/2013)
Eugene Elutin (7/25/2013)
webrunner (7/25/2013)
ScottPletcher (7/24/2013)
webrunner (7/24/2013)
Alexander Suprun (7/24/2013)
Remove ROLLBACK from the trigger and lower severity of RAISERROR to 10, in this case SSMS won't show any errors, rows will disappear from the UI (but actually they won't be deleted) and the attempt will be logged.Thanks!! That worked exactly the way I want it to.
So is the ROLLBACK statement really superfluous because it is an INSTEAD OF trigger?
Thanks again,
webrunner
ROLLBACK in this case is not "superfluous", it's downright dangerous.
You've got to remember that there could be multiple SQL statements -- DELETE and/or INSERT and/or UPDATE -- in the transaction, not just the DELETE you want to ignore. By explicitly issuing a ROLLBACK, you force the entire transaction to be rolled back.
Ah, right. Thank you so much for this point about triggers. I will keep that in mind as I work with triggers.
Thanks again,
webrunner
Which point? 😉
The point Scott mentioned that ROLLBACK would undo the whole transaction in a trigger. I'm sure that behavior may be called for in certain circumstances but I thanked Scott for reminding me that this will be the result.
webrunner
Until it is INSTEAD OF trigger! That what have surpised me. If you use INSTEAD OF trigger, modifications which happen after ROLLBACK are committed. That how you manage to see log records when you first added ROLLBACK into your trigger after RAISERROR and moved it before inserting into log.
Check this setup:
CREATE TABLE testDel (id int not null identity(1,1), val varchar(20))
CREATE TABLE logDel (id int not null identity(1,1), logtest varchar(2000))
GO
insert testdel (val) values ('aaaa'),('bbbb'),('cccc'),('eeee'),('ffff')
GO
CREATE TRIGGER [dbo].[tr_d_testDel] ON [dbo].[testDel]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
RAISERROR('Deletion of records from testDel is not allowed.', 16, 1)
ROLLBACK
INSERT INTO logDel ( logtest )
SELECT 'Attempted del of id: ' + cast(id as varchar(13)) + ' by ' + SYSTEM_USER
FROM deleted
RETURN;
END
GO
BEGIN TRANSACTION
INSERT INTO logDel ( logtest ) SELECT 'test'
DELETE testdel where id between 2 and 4
IF @@error != 0
BEGIN
PRINT 'Rollback'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT 'Commit'
COMMIT TRANSACTION
END
GO
If you execute the above, you will see that 'test' will not be found in the log, but three records for deleted one will be there.
Now if you change this trigger into ordinary FOR DELETE, you will not see any records there, as a WHOLE transaction will be rolledback.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply