Bug #25435 All triggers evaluated for statement even if out of scope of the statement
Submitted: 5 Jan 2007 15:11 Modified: 10 Jan 2007 17:26
Reporter: Mark Leith Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.32, 5.1.14 OS:Any (Any)
Assigned to: Marc Alff CPU Architecture:Any
Tags: triggers

[5 Jan 2007 15:11] Mark Leith
Description:
All triggers are evaluated on each statement against a table, even if they are out of scope of the current statement. 

For instance BEFORE UPDATE, and BEFORE DELETE triggers are checked even on an INSERT statement. 

We could get better performance were we to only check the appropriate triggers. 

How to repeat:
DELIMITER //

DROP TABLE IF EXISTS t1//
CREATE TABLE t1 (id int)//

/* make sure these tables do not exist */
DROP TABLE IF EXISTS foo//
DROP TABLE IF EXISTS bar//

CREATE TRIGGER tr1 BEFORE UPDATE ON t1 
  FOR EACH ROW
BEGIN
  INSERT INTO foo VALUES ('foo');
END//

INSERT INTO t1 VALUES (1)//

DROP TRIGGER tr1//

CREATE TRIGGER tr2 BEFORE DELETE ON t1
  FOR EACH ROW
BEGIN 
  INSERT INTO bar VALUES ('bar');
END//

INSERT INTO t1 VALUES (1)//

Suggested fix:
Only check triggers that are within scope of the current statement.
[10 Jan 2007 17:26] Marc Alff
Closing as a duplicate of Bug#21825.