MySQL if statement trigger

Question

Im trying to have trigger that when seats value is inserted is must be less than 31, the triggers doesn't work for some reason .. any help will be great as I tried other solutions but din work well

delimiter |

CREATE TRIGGER seats_check BEFORE INSERT ON booked
  FOR EACH ROW
  BEGIN
  if (new.seats_booked <=30)
  THEN INSERT INTO booked set seats_booked= new.seats_booked;
  ELSE
    INSERT INTO booked set seats_booked=0;
  end if;
  END;
|

didn't work? So an error? Unexpected results? Be precise? Also mysql-8.0.16+ has [check constraints](https://dev.mysql.com/doc/refman/8.0/en/alter-table.html) as does [mariadb-10.2.1](https://mariadb.com/kb/en/library/check-constraints-support/) — danblack, Apr 19 '20 at 07:04
You cannot action the table which fired the trigger in the trigger (including inserting) - you can on an insert trigger adjust the NEW. values and that's what you should be doing. — P.Salmon, Apr 19 '20 at 07:08
@danblack when data is inserted i get this error: #1442 - Can't update table 'booked' in stored function/trigger because it is already used by statement which invoked this stored function/trigger — Adnan Ali, Apr 19 '20 at 07:10
@P.Salmon Can you provide better example or point out the mistake. i have a similar trigger that updates value for another table but that works fine . all I need to do here is match the seats_booked column to specific value — Adnan Ali, Apr 19 '20 at 07:11
`IF NEW.seats_booked > 30 THEN SET NEW.seats_booked = 0; END IF;` — Nick, Apr 19 '20 at 07:18
you cannot update the same table on which the trigger occurs. This is what the error message says. Your other example probably updates a different table. See [the examples](https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html) `SET NEW.seats_booked=0`. no inserts. — danblack, Apr 19 '20 at 07:19
Alternatively, you could forget about the trigger, and just build the logic into the insert itself — Strawberry, Apr 19 '20 at 07:24
@Strawberry it might not work that well but lemme try to work on it — Adnan Ali, Apr 19 '20 at 07:27
@danblack is it possible to delete the record instead of setting it to 0? — Adnan Ali, Apr 19 '20 at 07:37
@Nick is it possible to delete the record instead of setting it to 0? — Adnan Ali, Apr 19 '20 at 07:37
@AdnanAli please see [this answer](https://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions/22489342#22489342) which explains how to cancel the insert if a given condition is true — Nick, Apr 19 '20 at 07:39
@Nick is that possible i set trigger/constraint that will copy value of or column to another dynamically eg: the result from seats.booked to remaining_seats column — Adnan Ali, Apr 19 '20 at 08:27
@AdnanAli possibly. I suggest you ask another question, I don't think you can give enough detail in a comment. — Nick, Apr 19 '20 at 08:29
@Nick the question is posted already have a look . https://stackoverflow.com/questions/61307448/using-trigger-to-make-sure-inserted-data-doesnt-cross-limit — Adnan Ali, Apr 19 '20 at 17:01

MySQL if statement trigger

0 Answers0