Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a stored procedure that is supposed to delete several rows from a table. It does not work and gives me the error:

The transaction ended in the trigger. The batch has been aborted

I tried running the individual queries to see if they would work separately. I found that the error is that it doesn't let me delete more than 1 row at a time. Here is an example:

DELETE FROM DetalleBusqueda
WHERE IdResultado IN (106577, 106578, 106579)

That query does not work, but the following one does:

DELETE FROM DetalleBusqueda
WHERE IdResultado IN (106577)

The first query is supposed to delete 3 rows, and the second one only 1 row. The second one does what it's supposed but the first one gives me the aforementioned error.

Does anyone have any idea what is it that I have to do so the procedure can delete more than a row at a time?

share|improve this question
1  
What is the target database management system? –  billinkc Dec 19 '12 at 22:34
    
Also, look at the table DetalleBusqueda Is a trigger on there? –  billinkc Dec 19 '12 at 22:38
11  
Sounds like your trigger is coded to only expect one row in the DELETED table. Please post the code for the trigger. –  Martin Smith Dec 19 '12 at 23:24

1 Answer 1

SQL Server does not support FOR EACH ROW clause in trigger. So, I think that query in a trigger falls down with an error like this

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Maybe replacement of "=" by "IN" will help.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.