I have two tables call it - table_1 and table_2

I have an After insert trigger on table_1 and in the trigger I insert on table_2 which has some fk constraints on another table_3.

When the insert on table_2 fails, the table_1 insert fails.

Is this normal? and Is there a workaround for this?



The entire trigger chain constitutes one atomic operation inside a transaction -- that's "A" in ACID. So yes, this is working as designed.

If atomicity is not important to you, that is, if you are fine with the fact that table_1 is updated but the insert into table_2 is lost, you need to introduce error handling in your trigger, something along these lines:

create trigger table_1_trig1 after insert on table_1 
for each row
 -- no-op handler that just continues; 
 -- sqlstate 23000 is the foreign key violation
 declare continue handler for sqlstate '23000' begin end;
 insert into table_2 ...;

dbfiddle link

The condition for sqlstate '23000' is the SQL standard equivalent of the MySQL-specific for 1452. If you want to ignore all errors when inserting into table_2 just use for sqlexception instead.

So yes, there is a workaround. However, one should think twice before going that route; if one doesn't care if rows are inserted into table_2 or not, why bother inserting them in the first place? May be take another look at your business logic.

Note that such error handling may not necessarily work in other DBMSes; MySQL treats trigger code much like stored procedures invoked upon DML execution, with all associated overhead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.