3

I'm really bad at SQL, but I'm trying to keep my database simple and minimalistic as possible. Not duplicating values but using references and id's instead.

Now after inserting a query I get error

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

Query can be inserted when the trigger is disabled. But my query is only reading from table 'users' so there should not be a conflict. I read something about deadlock - the query is locking table while using it or something like that and therefore a trigger cannot update a row? Ideas how could I achieve this?

I simplified my query so only important things are there.

Query:

INSERT INTO signed (time_of_start, player)
    SELECT time_of_game_id, users.user_id 
    FROM time_of_game, users
    WHERE time_of_game.time_of_start = "2017-02-01 12:00:00"
      AND users.steamid = "1234567890123456";

Trigger:

CREATE TRIGGER `payment_for_joining` 
AFTER INSERT ON `signed`
FOR EACH ROW 
    UPDATE users  
    SET users.credit = users.credit-1
    WHERE users.user_id = NEW.player
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vergil333
  • 505
  • 1
  • 7
  • 14

1 Answers1

1

It won't allow you to update the table because it's already being read by the INSERT INTO.. SELECT query that invokes this trigger.

An alternative way would be to disable the trigger and update the users table separately, e.g.:

INSERT INTO signed (time_of_start, player)
SELECT time_of_game_id, users.user_id FROM time_of_game, users
WHERE time_of_game.time_of_start="2017-02-01 12:00:00"
AND users.steamid="1234567890123456";

UPDATE users join time_of_game SET users.credit = users.credit-1
where time_of_game.time_of_start="2017-02-01 12:00:00"
AND users.steamid="1234567890123456";

You can add a join column with ON clause if there is any column that links these two tables.

Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • Yep, I was just hoping I can do this 2 operations with only one request/query. But I'm gonna take a tip from the more experienced programmer, I use your solution :) Thanks – Vergil333 Jan 09 '17 at 07:02
  • Guessed so. Then whats the use of nice triggers if we can use our application code to do things? Just asking for Mysql writers. I will also do my own after-insert routines in my php code. And I will comment on my table description what is going on after insert. – Ishmael Mavor Raines Oct 17 '22 at 14:27