MySQL Bugs: #15686: Probleme trigger

Bug #15686	Probleme trigger
Submitted:	12 Dec 2005 15:54	Modified:	22 Feb 2006 0:03
Reporter:	benhamou ariel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.16/5.0.18 BK	OS:	Windows (Windows 2003/Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
here my trigger :
create trigger finmois_detail_insert_montant AFTER INSERT ON finmois_detail
FOR EACH ROW
UPDATE finmois_base set total=(select SUM(montant) from finmois_detail where num_sim=NEW.num_sim) where num_sim=NEW.num_sim;

here my requete :

insert into finmois_detail (num_sim,montant) select num_sim,10 from finmois_base where code=431

the turned over message is: 

Can't update table 'finmois_base' in stored function/trigger becouse it is already used by statement which invoked this stored function/trigger.

How to repeat:
You must create two tables:
create table finmois_base (id_fin_base int(8) primary key auto_increment,num_sim varchar(30) not null,key num_sim(num_sim),code_mois int(8) not null default 0,total int(8) not null default 0);

create table finmois_detail (id_fin_detail int(8) primary key auto_increment,num_sim varchar(30) not null,key num_sim(num_sim),montant int(8) not null);

You must create trigger :
create trigger finmois_detail_insert_montant AFTER INSERT ON finmois_detail
FOR EACH ROW
UPDATE finmois_base set total=(select SUM(montant) from finmois_detail where num_sim=NEW.num_sim) where num_sim=NEW.num_sim;

then you owe inserer of the values in the table finmois_base and finmois_detail

Suggested fix:
Enough important because one donot can make correct analyses

Could you please provide the complete test case, I was unable to repeat
with latest source's server inserting values on my own.

Thanks in advance.

modification of the table finmois_base  :
create table finmois_base (num_sim
varchar(30) primary key,key num_sim(num_sim),code_mois int(8) not null default
0,total int(8) not null default 0);

here insertions :
insert into finmois_base (num_sim,code_mois) values('1',431)
insert into finmois_base (num_sim,code_mois) values('2',431)
insert into finmois_base (num_sim,code_mois) values('3',431)
insert into finmois_base (num_sim,code_mois) values('4',431)

When you carry out this requete, you have the message 
insert into finmois_detail (num_sim,montant) select num_sim,1000 from finmois_base

here the exact error message 
ERROR 1442 (HY000): Can't update table 'finmois_base' in stored function/trigger
 because it is already used by statement which invoked this stored function/trig
ger.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot db7;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.18-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table finmois_base (num_sim
    -> varchar(30) primary key,key num_sim(num_sim),code_mois int(8) not null default
    -> 0,total int(8) not null default 0);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into finmois_base (num_sim,code_mois) values('1',431)
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into finmois_base (num_sim,code_mois) values('2',431);
Query OK, 1 row affected (0.01 sec)

mysql> insert into finmois_base (num_sim,code_mois) values('3',431);
Query OK, 1 row affected (0.00 sec)

mysql> insert into finmois_base (num_sim,code_mois) values('4',431);
Query OK, 1 row affected (0.01 sec)

mysql> create table finmois_detail (id_fin_detail int(8) primary key
    -> auto_increment,num_sim varchar(30) not null,key num_sim(num_sim),montant int(8)
    -> not null);
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger finmois_detail_insert_montant AFTER INSERT ON finmois_detail
    -> FOR EACH ROW
    -> UPDATE finmois_base set total=(select SUM(montant) from finmois_detail where
    -> num_sim=NEW.num_sim) where num_sim=NEW.num_sim;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into finmois_detail (num_sim,montant) select num_sim,1000 from
    -> finmois_base;
ERROR 1442 (HY000): Can't update table 'finmois_base' in stored function/trigger because  <cut>

The probléme is very important because I am to block on updates A to make important

I have also the same problem. The table name is used for only selection not for update. So we must be able to select and update.

I've struggled with this limitation as well--we've had to dump the trigger logic into the application code until this is fixed.

A more helpful title for this bug might be "Cannot join against trigger table while modifying another table in a trigger."

I dont think this is a bug.  Its clearly stated in MySql manual under trigger limitations that one can not select from the table on which the trigger is firing from within that trigger.
As far as I know, this not allowed in any database, for example Oracle will generated table mutating error under similar circumstances.

Yes Mr. Murthy Vempaty's last comment is right. Thank you.

Hi, Benhamou Ariel and other guys!

You are hitting the following limitation of MySQL - in function or trigger you should not modify table which is modified or JUST READ by statement (or by any statement from the chain of statements) that invoke this function or trigger.

I have asked our Documentation team to emphasize this limitation in our manual.

Ah, I actually misread the original test case, which I see is problematic. The limitation I noticed, returning the same error, is that an UPDATE query within a trigger cannot join against the trigger table using the UPDATE t1 JOIN t2 syntax even if the columns that are actually changed are not in the trigger table (or any chained tables). However, using a subquery instead of a join does work and is functionally identical.

I have a test case that I could post as a separate bug, but the subquery works just as well, so it's not that serious a limitation...