Bug #41298 Trigges "before/after delete" do not fire when deletion caused by fk-constraint

• Submitted: 8 Dec 2008 11:28
• Modified: 24 Aug 2009 19:55
• Reporter: Robert Senger
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.1.30
• OS: Linux
• CPU Architecture: Any

[8 Dec 2008 11:28] Robert Senger

Description:
The Triggers "before delete" or "after delete" do not fire if deletion of a row is caused by an foreign key constraint "on delete cascade".

Triggers on table xxx work fine if rows are deleted by a "delete from xxx;" statement. If rows are deleted by a foreign key constraint "on delete cascade", triggers do not fire as they should.

How to repeat:
Tables and Triggers:

create table Parent
(
 ID int not null,
 primary key (ID)
)
engine=innodb;

create Table Child
( 
 ID int not null,
 ParentID int not null,
 primary key (ID),
 foreign key (ParentID) references Parent (ID) on delete cascade
)
engine=innodb;

create table Counter
(
 NumChild int
)
engine=innodb;
insert into Counter values (0);

delimiter |
create trigger ChildInsert after insert on Child
for each row begin
update Counter set NumChild=NumChild+1;
end;
create trigger ChildDelete after delete on Child
for each row begin
update Counter set NumChild=NumChild-1;
end;
delimiter ;

Code to Reproduce:

insert into Parent values (1);
insert into Child values (1, 1);

select * from Child;

+----+----------+
| ID | ParentID |
+----+----------+
|  1 |        1 |
+----+----------+
1 row in set (0.01 sec)

select * from Counter;
+----------+
| NumChild |
+----------+
|        1 |
+----------+

delete from Child where ChildID=1;
select * from Child;
Empty set (0.00 sec)

select * from Counter;

+----------+
| NumChild |
+----------+
|        0 |
+----------+

---->>> CORRECT! Trigger "after delete on Child" fired.

insert into Child values (1, 1);

select * from Counter;

+----------+
| NumChild |
+----------+
|        1 |
+----------+

delete from parent where ParentID=1;
select * from Child;
Empty set (0.00 sec)

select * from Counter;
+----------+
| NumChild |
+----------+
|        1 |
+----------+

---->>> WRONG! Child row was deleted, Trigger "after delete on Child" did NOT fire.

Suggested fix:
Triggers "before/after delete" should fire when a row is deleted, regardless of the cause of deletion.

[8 Dec 2008 12:00] Valeriy Kravchuk

This is known and documented limitation. Read http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html:

"Triggers currently are not activated by foreign key actions."

[22 Apr 2009 1:01] Paul Whipp

This is a pretty fundamental problem with triggers. It means that they only 'sometimes' do what the developer would invariably intend.

I believe this bug should be raised in terms of its severity because it prevents robust software development using triggers to maintain integrity (e.g. where a row in a 'parent' table should be deleted only when there are no remaining related rows in a 'child' table).

[24 Aug 2009 19:55] Omer Barnir

This bug is a duplicate of bug#11472