2

In MySQL 5.5, I know I can prevent an insert by using SIGNAL, which rolls back the current transaction of my INSERT.

The fact is I want to use a trigger to insert a row in another table, but I'd like that to replace the original insertion. If I use SIGNAL in the trigger the new insert statement is also cancelled.

Example:

If I do

INSERT INTO table_a VALUES('a','b');

I want to have a new row in a table_b but none in the table_a.

(I want to replace the original insertion only in a few cases.)

Is it possible in MySQL? If yes, how can I do this?

Charles Okolms
  • 45
  • 1
  • 8

2 Answers2

2

I am not readily availble to provide exact details but I believe 2 more ways are possible:

  1. Using blackhole engine on table_a
  2. Using proxysql

Using Blackhole engine

It seems to be the simplest solution.

The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it.

See MySQL 5.5 doc for more details.

First, if you need to keep the table_a you need to create a copy of it with the blackhole engine ; 

CREATE TABLE table_a(
    id    INT unsigned auto_increment primary key,
    field CHAR(5)
)Engine=InnoDB;

CREATE TABLE table_blackhole(
    id    INT unsigned auto_increment primary key,
    field CHAR(5)
)Engine=BLACKHOLE;

Then, create the trigger on table_blackhole.
Inside the trigger, use

  • an INSERT on table_a (for example) for the normal treatment (we don't prevent the insertion)
  • or an INSERTon table_b for the specific treatment (we "prevent" the normal insertion but there is no rollback).

Instead of inserting rows in table_a, do your INSERT on table_blackhole.

Charles Okolms
  • 45
  • 1
  • 8
user1235000
  • 36
  • 1
0

Back on 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

MySQL Stored Procedure Programming

under the subheading 'Validating Data with Triggers'

Note what the book (released April 4, 2006) says about SIGNAL

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 standard).

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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536