Ok, how about something like...

***************
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)
INSERT INTO SomeTable SELECT * FROM deleted WHERE SomeField IS NOT NULL
end
end
**************

BOb


>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?