How to commit transaction before raise in mysql trigger?

Question

CREATE TRIGGER maindb.before_acc_update
BEFORE UPDATE
ON maindb.acc FOR EACH ROW
BEGIN
DECLARE errorMessage VARCHAR(255);
DECLARE clientName VARCHAR(100);
SET clientName = (select USER());
SET errorMessage = CONCAT('DB Error=> new acc_name value (',
                          NEW.acc_name,
                          ') cannot be null or empty. Old Value is:',
                          OLD.acc_name);
IF new.acc_name is null or TRIM(COALESCE(new.acc_name , '')) = ''  THEN
    BEGIN
        INSERT INTO maindb.acc_log_error (old_tz, new_tz, username) values (old.acc_name , new.acc_name, clientName);
    END ;
    SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = errorMessage;
END IF;
END;

Mysql cancels my transaction. I'd like to log to maindb.acc_log_error and raise my custom error.

Answer 1

Sorry, if you raise a signal in a trigger, it aborts the action of that trigger, including all changes made by that trigge, e.g. your INSERT into the log table.

The only way you could keep the insert to the log table after the trigger aborts is to do that INSERT as a separate statement before or after your UPDATE to acc that spawned the trigger.