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?

3
  • 1
    Some things really are better done on application level. I'd say this is one of them.
    – tombom
    Feb 28, 2018 at 11:45
  • @tombom That's probably what we'll do ; I asked that because I like to deal with this kind of things directly in the database, especially in this case where the db is accessed by more than one application. This could simplify the queries from the different apps and their maintenance so I thought it'd be worth it to ask. Feb 28, 2018 at 13:07
  • 1
    Perhaps not necessarily application level. A stored procedure would seem an option to me too.
    – Andriy M
    Mar 1, 2018 at 11:51

2 Answers 2

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.

1
  • I really like the blackhole engine approach. We have to do a little change in structure (adding a blackhole table on which there'll be the trigger that inserts into A or B) but I think it's the simplest and the more efficient solution so far. Mar 6, 2018 at 13:35
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.

3
  • I guess it's one thing when you just need to prevent an action, and it's an entirely different matter when you have, say, two actions to deal with, of which one needs to be prevented and the other allowed to perform. Your linked answers clearly have to do with the former issue. They may well be relevant to the latter as well but you didn't test the suggested approach in the OP's scenario. I mean, you could've just commented with a "Related: {link(s) to your answer(s)}" kind of comment with the same effect.
    – Andriy M
    Mar 2, 2018 at 10:14
  • I tried some things with this idea of abortion procedure but had no chance with it. (Also I didn't have much time to test, so maybe I just misunderstood the way to do it) Anyway I think this shows it is not normally possible to prevent an insert from within a trigger without causing it to roll back. I prefer not to go too deep in workarounds that use MySQL's flaws. If I'd go for something suggested here, I'd probably chose to do a new table in blackhole engine as suggested by @user1235000 Mar 6, 2018 at 13:49
  • @CharlOkolms I don't blame you for thinking this way. It is probably better to write INSERTs of this type as a stored procedure rather than a clunky trigger. Mar 6, 2018 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.