Is It Possible for a MYSQL Trigger to Return an Error Message if the Trigger Fails for Any Reason?

Question

I am trying to create an after insert trigger in MySQL that writes the message "Insert Failed" to an error table called tbl_error if the trigger fails for any reason.

To solve this problem, I have attempted to use a combination of handlers and signal statements. None have worked.

DELIMITER $$
CREATE TRIGGER trig_statesout_afterinsert AFTER INSERT ON states
FOR EACH ROW
BEGIN
    
DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SIGNAL SQLSTATE VALUE '99999'
      SET MESSAGE_TEXT = 'Insert Failed';
    INSERT INTO tbl_error(ErrorMessage) Values ('Insert Failed');
  END;

INSERT INTO statesout (states_id, state, cases, lastupdate)
SELECT s.states_id, s.state, s.cases, current_timestamp()
FROM states as s
WHERE s.states_id = NEW.states_id;

END $$

I attempted to return information from a diagnostic statement, but that didn't work either.

Because the documentation states that

For SQLEXCEPTION conditions, the stored program terminates at the statement that raised the condition, as if there were an EXIT handler. If the program was called by another stored program, the calling program handles the condition using the handler selection rules applied to its own handlers.

I split the update logic for the trigger into a separate stored procedure in case the issue was that the SQLEXCEPTION conditions cannot be in the calling object. It didn't work.

Some SO solutions suggest using a "trick" to call a nonexistent table, but the documentation says this is not necessary due to the SIGNAL statement:

Without SIGNAL, it is necessary to resort to workarounds such as deliberately referring to a nonexistent table to cause a routine to return an error.

In any case, those solutions only throw errors based on a specific condition, not for any condition.

What am I doing wrong? Is it impossible for a MYSQL trigger to return an error message if the trigger fails for any reason?

score 1 · Accepted Answer · answered Jul 07 '20 at 03:06

It's not possible to make any change to data if the trigger activates a SQLException.

If the trigger experiences any error, whether handled or not handled, then the INSERT that spawned the trigger is undone, as well as any SQL run by the trigger, and even any SQL run by triggers spawned by actions your trigger executes.

This is related to the idea of atomicity — either the whole action must succeed, or else no part of the action is saved. It's all or nothing.

If you want to log the error, you'll have to check for the error in your application, and then log the error as a separate action. You can't do it in the trigger body.

Is It Possible for a MYSQL Trigger to Return an Error Message if the Trigger Fails for Any Reason?

1 Answers1