CODEWITHSUNDEEP
mysqltriggerssql-update
Oskar Persson
Oskar Persson

Reputation: 6755

MySQL: Update column with TRIGGER BEFORE INSERT

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?

Upvotes: 1

Views: 3450

Answers (1)

eggyal
eggyal

Reputation: 125865

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.

Upvotes: 3

Related Questions