Trigger error when rollback

Question

i have this trigger and it is returns error when rollback , the error is "The transaction ended in the trigger. The batch has been aborted."

create trigger trigger1 on payments
for insert
as
set nocount on ;
begin
    if(select COUNT(*) from customers c , inserted i where c.customer_id = i.customer_id) = 0

        begin
            rollback tran;
            print 'Customer not found'
        end
     else

        print 'ok'

end

Using triggers to rollback an external transaction isn't a good idea IMO as the [`@@Trancount` won't balance](http://technet.microsoft.com/en-us/library/ms187844(v=sql.105).aspx). Suggest either just raise an error / throw from the trigger, and calling proc should then rollback assuming it has an error handler, or better still, add the exists check to the insert proc / code - this will make it more clear to readers than a trigger. — StuartLC, Dec 31 '13 at 12:06
yes as mentioned already by @StuartLC, your rooling back in the trigger is the main cause for this error. See another post with nice explanation here http://stackoverflow.com/questions/7310820/transaction-has-ended-in-trigger-batch-has-been-aborted-derived-attribute — Rahul, Dec 31 '13 at 12:11
If you wanted to ensure that Payments.customer_id exists in customers, why don't you just add a FOREIGN KEY constraint? Besides, *what* do you want to achieve here, if you don't really want to rollback the transaction? — Panagiotis Kanavos, Dec 31 '13 at 12:29

Rahul · Answer 1 · 2013-12-31T12:35:51.733

As per comment try raising error from trigger instead of rolling back (OR) roolback and then throw/raise error and return from the trigger like below

create trigger trigger1 on payments
for insert
as
set nocount on;
begin
    if(select COUNT(*) from customers c join inserted i 
    on c.customer_id = i.customer_id) = 0
        begin
        declare @errmsg VARCHAR(MAX), @sev AS INT, @state AS INT
        SELECT @errmsg = ERROR_MESSAGE(), @sev = ERROR_SEVERITY(), 
        @state = ERROR_STATE()
        print 'Customer not found'
        rollback transaction
        raiserror(@errmsg,@sev,@state);
      return
        end
     else
        print 'ok'
end

The same error when inserting a customer number that is not existed 'The transaction ended in the trigger. The batch has been aborted.' — Interaoi, Dec 31 '13 at 12:48

Trigger error when rollback

1 Answers1