I don´t want trigger to rollback-CodePudding

Sooo, I´m writing in MySQL a trigger to count how many attempts of an insert query happened (even failed attempts) but so far nothing.

If the insert is succesful, the variable 'attempts' increases its value by one. But when the insert query fails (because you tried to insert something ilogical) the trigger makes a rollback and 'attempts' doesn't increase.

How to avoid the rollback? Or how to outsmart it so 'attempts' will increase?

Here is my code:

CREATE TABLE myData (myValues INT);
SET attempts =0;

DELIMITER |
CREATE TRIGGER countingAttempts BEFORE INSERT ON myData FOR EACH ROW 
    SET @attempts = @attempts 1;
DELIMITER ;

INSERT INTO myData VALUES(10); /* It works, attempts becomes 1*/
INSERT INTO myData VALUES (X); /* The insert query fails because 'myValues' is INT and X is not an INT, attempts should become 2, but the trigger rollsback and attemps doesn´t change*/

CodePudding user response：

It seems like you are trying to count attempted inserts that result in errors due to bad data (presumably with STRICT_TRANS_TABLES mode enabled).

There is no "rollback", since no insert was actually done. The trigger simply isn't executed by the point the error is detected. (Though if you are inserting multiple rows, the trigger will be executed for initial correct rows before the erroneous one, so you will see the variable increased in that case.)

You could experiment with disabling STRICT_TRANS_TABLES and doing some validation in your trigger instead, but that's going to have an effect on all other tables and update statements too, so I wouldn't recommend it.

The other option I see is to not do inserts from the client, but instead call a stored procedure to do the insert; that gives you a chance to increment your counter in the stored procedure whether the insert works or not.