Unable to delete a row using MySQL triggers

I have a trigger in MySQL

 DELIMITER $$ 
CREATE TRIGGER trigger2
BEFORE INSERT ON participated FOR EACH ROW
 BEGIN    
 IF((SELECT COUNT(*) FROM participated WHERE driver_id = NEW.driver_id) > 3) THEN 
     DELETE FROM accident WHERE report_no = NEW.report_no;         
SIGNAL SQLSTATE '45000' SET message_text = "Driver is already involved in 3 accciddents"; 
END IF; 
END;$$ 
DELIMITER ;

First an accident report is inserted into accident table. Before inserting in to participated table if it involves a driver who has participated in more than 3 accident a waring has to be given and driver's data in accident table should be deleted.

'accident' and 'participated' are the two tables.

 accident(report_no,date,location);
  participated(driver_id,reg_no,report_no,amount);

ex:

 insert into accident values(34,"2022-04-05","bangalore");

 insert into participated values("D1","KA-09-MM-5644",34,20000);

 ERROR 1644 (45000): Driver is already involved in 3 accciddents

Warning is working but it is not deleting the row in accident table. accident table still has the row with report_no 34

The body part in Mysql trigger is like a single ALL-OR-NOTHING transaction. This means every query inside has to be successful, or the entire process is undone. By using SIGNAL SQLSTATE '45000' SET message_text , an error is intentionally raised, which rolls back every thing that has happend so far, and a message is returned. Note, the INSERT statement itself is cancelled due to error induced. Of course, it's possible to ignore the error by declaring a continue handler in the very begining of the trigger.

BEGIN
declare continue handler for SQLSTATE '45000' begin end;    
 IF((SELECT COUNT(*) FROM participated WHERE driver_id = NEW.driver_id) > 3) THEN 
     DELETE FROM accident WHERE report_no = NEW.report_no;         
SIGNAL SQLSTATE '45000' SET message_text = "Driver is already involved in 3 accciddents"; 
END IF; 
END  

This will make sure things keep going after SQLSTATE '45000' is encountered. However, the message_text is IGNORED, as it's only intended to show up to address a warning/error, not for a continue handler. And regrettably, we can not return a result set using a trigger. So if we add a SELECT statement or something alike after the SIGNAL statement , an error will pop up:

select "Driver is already involved in 3 accciddents" as a warning; 
-- OR
show warnings;
-- Error Code: 1415. Not allowed to return a result set from a trigger

If we really need a message to show up,we can considering using a procedure to bypass the restriction enforced by trigger:

DELIMITER $$ 
CREATE TRIGGER trigger2
BEFORE INSERT ON participated FOR EACH ROW
 BEGIN    
 IF((SELECT COUNT(*) FROM participated WHERE driver_id = NEW.driver_id) > 3) THEN 
     DELETE FROM accident WHERE report_no = NEW.report_no;         
SET @warning = "Driver is already involved in 3 accciddents"; -- here we don't really need a SIGNAL statement. Just creating a user variable is adequate.
else set @warning=null;
END IF; 
END$$

create procedure insert_participated (d_id varchar(20),rg_no varchar(20),rp_no int,amt int)
begin
insert into participated values(d_id,rg_no,rp_no,amt);
if @warning is not null then 
select @warning as warning;
end if;
end $$ 
DELIMITER ;

By using a procedure ,we can display a message. And if we directly use an insert statement(when we forget to use the procedure), the trigger's operation still applies. Therefore, we might think about adding an INSERT statement to populate an auditing table for future reference.