MySQL After Insert trigger - how to handle case where no rows were inserted
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.