Often the spec requires that if an INSERT fails, the existing row be updated. A failed INSERT IGNORE cannot trigger an UPDATE, but as the following script shows, INSERT...ON DUPLICATE KEY... can:
DROP TABLE IF EXISTS log,ti,tm;
CREATE TABLE log (a varchar(200));
CREATE TABLE ti (a int PRIMARY KEY default 0, b int) ENGINE=InnoDB;
create trigger ti1 before insert on ti
for each row insert into log select concat("ti before insert ",new.a,',',new.b);
create trigger ti2 after insert on ti
for each row insert into log select concat("ti after insert ",new.a,',',new.b);
create trigger ti3 before update on ti
for each row insert into log select concat("ti before update ",new.a,',',new.b);
create trigger ti4 after update on ti
for each row insert into log select concat("ti after update ",new.a,',',new.b);
CREATE TABLE tm (a int PRIMARY KEY default 0, b int) ENGINE=MyIsam;
create trigger tm1 before insert on tm
for each row insert into log select concat("tm before insert ",new.a,',',new.b);
create trigger tm2 after insert on tm
for each row insert into log select concat("tm after insert ",new.a,',',new.b);
create trigger tm3 before update on tm
for each row insert into log select concat("tm before update ",new.a,',',new.b);
create trigger tm4 after update on tm
for each row insert into log select concat("tm after update ",new.a,',',new.b) ;
insert into ti select 1,1;
select * from log;
insert into ti select 1,1;
select * from log;
insert IGNORE into ti select 1,1;
select * from log;
insert into ti select 1,1 ON DUPLICATE KEY UPDATE b=b+1;
select * from log;
delete from log;
insert into tm select 1,1;
select * from log;
insert into tm select 1,1;
select * from log;
insert IGNORE into tm select 1,1;
select * from log;
insert into tm select 1,1 ON DUPLICATE KEY UPDATE b=b+1;
select * from log;
|
|