General information
Forum:
Microsoft SQL Server
Category:
Database management
Hi!
Thanxs for your replies, guys!
But I'm afraid I desribed my trouble too vaguely. The matter is: I know how to delete only rows matching the rule, but I don't know how to prevent entire tran from rollbacking if trigger encounters rows that DON'T match the rule. Trigger got to rollback DELETE statement that caused that trigger to fire and I the only way I know to do that is include ROLLBACK TRAN in trigger body. Look at my trigger code:
CREATE TRIGGER SomeTrigger ON SomeTable
FOR DELETE
AS
begin
if exists (SELECT * FROM deleted WHERE SomeField IS NOT NULL)
begin
raiserror('You tried to trash valuable data - it''s forbidden', 16, 1)
rollback tran -- That is the nasty line!
DELETE SomeTable FROM SomeTable INNER JOIN deleted ON deleted.id = SomeTable.id WHERE deleted.dt IS NULL
end
end
Everything workds fine in cases when user tries to execute standalone SQL statements like
DELETE SomeTable
or
DELETE SomeTable WHERE
but in cases when user tries to put DELETE statement inside a batch or tran and that DELETE statement trigger finds out that user tried to delete forbidden rows it displays warning message, rollbacks tran and deletes only rows that can be deleted according the rule. But it rolls back entire batch or tran it was placed into! Look at the example:
begin tran
-- The lines before DELETE statement will be processed
SELECT * FROM SomeTable
DELETE SomeTable -- Assume there're lines both matching and not matching the rule in SomeTable
-- In such case following lines won't be processed due to ROLLBACK TRAN statement issued from the trigger fired by DELETE
SELECT * FROM SomeTable -- User will never see the result set from this line!
end tran
What I want is batch or tran complete in ANY case, i.e. trigger rolls back only statement that caused it to fire (in the example it's DELETE statement) BUT NOT the entire batch or tran! And I would like to write the code of trigger independent of any code of possible batches or trans. Sure, I could write SAVE TRAN before DELETE statement in the abovedescribed example and in case of errors rollback only savepoint, but it makes trigger dependent on code of batch. So the trouble is:
Is there in SQL Server possibility to roll back only statement that caused trigger to fire?
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only