MySQL: Update column with TRIGGER BEFORE INSERT

Question

I have this code that prevents duplicates if column C is equal to a specific value

DELIMITER $$

CREATE TRIGGER special_unique_test BEFORE INSERT ON myTable 
    FOR EACH ROW BEGIN
       IF NEW.C = 'x' AND EXISTS (
          SELECT * FROM myTable WHERE A = NEW.A AND B = NEW.B AND C = 'x'
       )THEN

          UPDATE myTable SET D = D + 1 WHERE A = NEW.A AND B = NEW.B AND C = 'x';

       END IF;
    END$$

DELIMITER ;

Everything works except that the update statement isn't executed.

ADDED

Tried this in PhpMyAdmin and I got this error:

1442 - Can't update table 'myTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I could go around this by making a separate table for the D column but it would be nice to have it in the same table.

Extra question: How can I make this error show when I insert something from my website using mysqli?

Community · Accepted Answer · 2017-03-20 10:29:31Z

3

As documented under Restrictions on Stored Programs:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

If you want to increment D only in the newly inserted row, you merely need SET NEW.D = NEW.D + 1;. If you wish to increment D for every 'colliding' record, you will need to store D in some other (related) table. For example, you could store column D in a separate table with FK (A, B, C) back into myTable.

However one can't help but wonder whether there's a better way to achieve your overall goal: what exactly is the business problem that you are trying to address with this trigger? Beware of the XY problem.

edited Mar 20, 2017 at 10:29

CommunityBot

11 silver badge

answered Oct 14, 2012 at 22:26

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oskar Persson Over a year ago

A basic answer to what I want to achieve is that I have a table where you can only add data. I then want to make the combination between 2 columns unique if the 3rd one is equal to a specific value. If the same data is inserted again I want the counter to increase each time.

eggyal Over a year ago

@Oskwish: That's the technical problem that you're trying to address, not the underlying business problem. Why do you want to do this? Please read that link explaining the XY problem.

Collectives™ on Stack Overflow

MySQL: Update column with TRIGGER BEFORE INSERT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related