Apr 25, 2011, I answered the post Trigger in MySQL to prevent insertion
I learned the technique of making a Stored Procedure abort without SIGNAL from
Chapter 11, Pages 254-256 of the Book
under the subheading 'Validating Data with Triggers'
Note what the book (released
April 4, 2006) says about
The reason the book suggests preempting the Trigger in this manner
stems from the fact that the MySQL Stored Procedure Language did not
have SIGNAL implemented into the language (of course, SIGNAL is ANSI
The authors of the book created work arounds by calling on SQL
statements that are syntactically correct but fail at runtime. Pages
144-145 (Chapter 6: Error Handling) of the book gives these examples
on preempting a Stored Procedure directly (Example 6-18) or by SIGNAL
emulation (Examples 6-19 and 6-20).
An example of how to do this is in check constraint does not work? (
Dec 23, 2011)
I have also explained this in my post BEFORE INSERT trigger in MySQL (
Jun 09, 2013)
In your particular case, you need to add the new row to
table_b and then abort the stored procedure. I cannot guarantee the rollback won't happen but at least no
SIGNAL should occur. You will have to experiment with this and see.