Manage INSERT failures

from the Artful MySQL Tips List


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;



Return to the Artful MySQL Tips page