Bug #6812 Triggers are not activated for INSERT ... SELECT

• Submitted: 24 Nov 2004 18:13
• Modified: 1 Jun 2005 3:52
• Reporter: Peter Gulutzan
• Status: Closed
• 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

[24 Nov 2004 18:13] Peter Gulutzan

Description:
I have a BEFORE INSERT trigger, it should increment a variable every time a row is 
inserted in table t. It works okay when I use INSERT ... VALUES. But nothing happens when 
I use INSERT ... SELECT. 
 

How to repeat:
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> create trigger t_bi before insert on t 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> insert into t values (0); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> select @x; 
+------+ 
| @x   | 
+------+ 
| 1    | 
+------+ 
1 row in set (0.00 sec) 
 
mysql> insert into t select * from t; 
Query OK, 1 row affected (0.00 sec) 
Records: 1  Duplicates: 0  Warnings: 0 
 
mysql> select @x; 
+------+ 
| @x   | 
+------+ 
| 1    | 
+------+ 
1 row in set (0.00 sec)

[25 May 2005 4:09] Dmitry Lenev

Fixed in 5.0.7

[1 Jun 2005 3:52] Paul DuBois

Noted in 5.0.7 changelog.