MySQL After Insert trigger - how to handle case where no rows were inserted UPDATED based on Akina's answer -

In MySQL the After Insert trigger fires for every insert call, even in cases where the insert fails and no rows are inserted. While any changes made by the trigger are rolled-back by MySQL on failure, external service calls made by the trigger may cause changes that need to also be rolled back.

Is there a way to catch this edge case in the trigger so it can be handled?

For ref my general trigger format is -

CREATE TRIGGER `db`.`table_name_AFTER_INSERT` AFTER INSERT ON `table_name` FOR EACH ROW
BEGIN

-- HANDLE ERRORS
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
BEGIN
    get diagnostics condition 1
      @p1 = MESSAGE_TEXT,
      @p2 = RETURNED_SQLSTATE,
      @p3 = MYSQL_ERRNO,
      @p4 = SCHEMA_NAME,
      @p5 = TABLE_NAME;
    INSERT INTO `db_errors_table_name` (`location`, `error`)
        VALUES ('Some descriptive text here regarding loc', @p1);
END;

-- Call external service here

END
the After Insert trigger fires for every insert call, even in cases where the insert fails and no rows are inserted


That's not right. 

Single query is a transaction, trigger action is a part of it, if fail the whole transaction is rollbacked, including changes made by trigger. 




create table test (id int not null primary key, val int);



✓






create table log(id int, val int, cnt int not null auto_increment primary key);



✓






create trigger tr after insert on test for each row 
insert into log (id, val) values (new.id, new.val);



✓






insert into test values (1,1), (2,2);



✓






select * from test;



id | val
-: | --:
 1 |   1
 2 |   2






select * from log;



id | val | cnt
-: | --: | --:
 1 |   1 |   1
 2 |   2 |   2






insert into test 
select 3,3 union all select 2,4 union all select 5,5 order by 2;



Duplicate entry '2' for key 'PRIMARY'






select * from test;



id | val
-: | --:
 1 |   1
 2 |   2






select * from log;



id | val | cnt
-: | --: | --:
 1 |   1 |   1
 2 |   2 |   2






insert into log (id, val) values (6,6);



✓






select * from log;



id | val | cnt
-: | --: | --:
 1 |   1 |   1
 2 |   2 |   2
 6 |   6 |   4



db&lt;>fiddle here

Pay attention to the hole in log.cnt values. Autoincrement value 3 is absent because it was used during 2nd insertion and then it was removed during rollback caused by duplicate error.