How to cancel an INSERT trigger event?

Question

How to cancel an INSERT trigger event?

Navigation

#1 by (0 votes)

1

Hello, I created a trigger that every time the user makes a sale is debited the amount of items, but if I do not take care the amount can be negative because the user can sell more than he has in the database. So I need a trigger that does not INSERT that sale if the amount of the sale is greater than the one available. For this I would need to test 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

mysql eventos trigger

asked by anonymous 01.01.2018 / 17:58

1 answer

Send email with attachments in php Show data coming from a JSON in a listview

score 0 · Answer 1

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

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

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

¹ Avoid playing such tricks.