Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Can I perform Conditional Delete?
Message
From
16/11/2000 01:27:25
 
 
General information
Forum:
Microsoft SQL Server
Category:
Database management
Miscellaneous
Thread ID:
00441994
Message ID:
00442503
Views:
9
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
Map
View

Click here to load this message in the networking platform