MySQL Bugs: #5890: Triggers fail for DELETE

Bug #5890	Triggers fail for DELETE
Submitted:	5 Oct 2004 1:00	Modified:	12 Nov 2004 15:04
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Triggers for BEFORE DELETE and AFTER DELETE don't appear to be activated. 

How to repeat:
mysql> create table x6 (col1 int);// 
Query OK, 0 rows affected (0.26 sec) 
 
mysql> insert into x6 values (1),(2),(3),(4),(5);// 
Query OK, 5 rows affected (0.00 sec) 
Records: 5  Duplicates: 0  Warnings: 0 
 
mysql> create trigger x6_bd before delete on x6 for each row set @x = @x + 1;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> set @x = 0;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> delete from x6;// 
Query OK, 5 rows affected (0.26 sec) 
 
mysql> select @x;// 
+------+ 
| @x   | 
+------+ 
| 0    | 
+------+ 
1 row in set (0.00 sec)

Verified with 5.0.2-alpha-debug-log

It appears that DELETE BEFORE and AFTER triggers actually
are activated, but only if the DELETE statement includes
a WHERE clause.

Here is a short script that demonstrates the problem.  For the
first DELETE, neither of the triggers activate.  For the second
DELETE, both the BEFORE and AFTER triggers activate.

use test;
drop table if exists t;
create table t (i int);

create trigger t_del_before before delete on t for each row
set @del_before = 1;
create trigger t_del_after after delete on t for each row
set @del_after = 1;

# with no WHERE clause, the DELETE triggers do not activate
set @del_before = NULL, @del_after = NULL;
insert into t (i) values (1);
select i from t;
delete from t;
select @del_before, @del_after;
-- the variables both will be NULL here
select i from t;

# with a WHERE clause, the DELETE triggers do activate
set @del_before = NULL, @del_after = NULL;
insert into t (i) values (1);
select i from t;
delete from t where i > 0;
select @del_before, @del_after;
-- the variables both will be 1 here
select i from t;

drop trigger t.t_del_before;
drop trigger t.t_del_after;

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet 1.1644.1.5 2004/11/12 17:04:07 dlenev@brandersnatch.localdomain
  Fix for bug #5890 "Triggers fail for DELETE without WHERE".
  
  If we have DELETE with always true WHERE clause we should not use 
  optimized delete_all_rows() method for tables with DELETE triggers,
  because in this case we will lose side-effect of deletion.