mysql – How to cancel an INSERT event by trigger?

Question:

Hello, I created a trigger that every time the user makes a sale, the amount of items is debited, but if I'm not careful, the amount can be negative because the user can sell more than they have in the database . So I need a trigger that doesn't INSERT that sale if the sale amount is greater than the available amount. For that I would need to test it before… I started something like this

--Esse é um trigger BEFORE INSERT
    BEGIN
    set @qtd = SELECT itens.quantidade WHERE itens.id = NEW.itens_id;
    IF (NEW.quantidade > @qtd) THEN
        --Cancela o INSERT para que o Trigger do AFTER INSERT n faça a subtração ficando negativa no estoque 
    END IF;

    END

Answer:

It is not possible to stop an INSERT by TRIGGER (or at least not without using a workaround). You have two options in this case:

  1. Check the quantity before entering the record and display a message to the user;

  2. You can generate an error message using SIGNAL , handle this error in your backend and display a message to the user¹

¹ Avoid doing tricks of this type.

Scroll to Top