Sql-server – Error deleting more than 1 row

deleteerrorssql server

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?

Best Answer

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.

Related Solutions

SQL Server – Why Order of Parameters Matters for sp_trace_create

I believe this is because it is an extended stored procedure and the parameter names are actually entirely ignored. It just goes off position.

I have renamed them as below (and given them all the same name) and it still works fine.

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @rubbish = @new_trace_id OUTPUT,
  @rubbish = 0,
  @rubbish = N'C:\temp\TestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;

A similar documentation bug was filed by Aaron about sp_executesql.

Another annoying aspect of that stored procedure is that the @maxfilesize must be passed as 'bigint' and it doesn't accept a literal integer. I assume that this is also because it is an extended stored procedure.

Mysql – Error “#1118 – row size too large” on the first row of the table only

As suggested from this bug report, you may need to look into the following:

File Format

Run SHOW VARIABLES LIKE 'innodb_file_format%';
- innodb_file_format
- innodb_file_format_max
You might see values Antelope, Barracuda

Row Format

Run ALTER TABLE tblname ROW_FORMAT=DYNAMIC; to make the row format Dynamic
Run ALTER TABLE tblname ROW_FORMAT=COMPRESSED; to make the row format Compressed

Suggestions

Workaround: use new InnoDB file format Barracuda and row_format=DYNAMIC

This was added to the change log in MySQL 5.5.29

Perhaps an upgrade to the latest MySQL 5.5. is in order

Give it a Try !!!