Description:
TRUNCATE still fires ON DELETE triggers if the engine resorts to row-by-row delete; this is the case for an InnoDB table referenced by a foreign key.
To see it, I patched trigger.test of 5.1 like this:
===== t/trigger.test 1.67 vs edited =====
*** /tmp/bk_trigger.test-1.67_3DorkS 2007-02-02 07:19:31 +01:00
--- edited/t/trigger.test 2007-04-16 15:37:14 +02:00
***************
*** 1,3 ****
--- 1,5 ----
+ -- source include/have_innodb.inc
+
# This test uses chmod, can't be run with root permissions
-- source include/not_as_root.inc
***************
*** 1508,1515 ****
#
# Bug#23556 TRUNCATE TABLE still maps to DELETE
#
! CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW
--- 1510,1519 ----
#
# Bug#23556 TRUNCATE TABLE still maps to DELETE
#
! CREATE TABLE t1 (a INT PRIMARY KEY) engine=innodb;
CREATE TABLE t2 (a INT PRIMARY KEY);
+ CREATE TABLE t3 (a INT PRIMARY KEY, t1_a INT, INDEX (t1_a),
+ FOREIGN KEY (t1_a) REFERENCES t1(a)) engine=innodb;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW
***************
*** 1529,1535 ****
SELECT COUNT(*) FROM t2;
DROP TRIGGER trg_t1;
! DROP TABLE t1,t2;
#
# Bug #23651 "Server crashes when trigger which uses stored function
--- 1533,1539 ----
SELECT COUNT(*) FROM t2;
DROP TRIGGER trg_t1;
! DROP TABLE t3,t1,t2;
#
# Bug #23651 "Server crashes when trigger which uses stored function
then I observe 8 rows in t2 (trigger was fired) instead of 0.
Indeed, in mysql_delete() we come to the part which does row-by-row delete and which fires triggers.
How to repeat:
see description, trigger.test.
