MySQL 5.5 – Prevent Insert Without Rolling Back

MySQLmysql-5.5trigger

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?

Best Answer

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.

Related Question