CODEWITHSUNDEEP
mysqlsql
paco666
paco666

Reputation: 5

Create trigger that delete after insert

I want to create a trigger that delete any new row I insert if the value is more than a number, for example 8.

I tried this:

DELIMITER |
CREATE TRIGGER mytrigger AFTER INSERT ON mytab
    FOR EACH ROW 
    BEGIN
        DELETE FROM mytab WHERE myparameter > 8;
    END
|

DELIMITER ;

I tried to:

INSERT INTO mytab VALUES (9);

and this is the message error:

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

also if I put:

INSERT INTO mytab VALUES (4);

it runs the same error...

Thanks!

Upvotes: 0

Views: 537

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Don't delete! Just produce an error on the insert:

DELIMITER |
CREATE TRIGGER mytrigger BEFORE INSERT ON mytab
    FOR EACH ROW 
    BEGIN
        IF new.myparameter > 8 THEN
             signal sqlstate '45000' set message_text = 'Parameter value can be no larger than 8';
        END IF;
    END
|

DELIMITER ;

Upvotes: 2

Related Questions