Create trigger that delete after insert

Question

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!

you can not do insert/update/delete on the same table where you are running the trigger. — Abhik Chakraborty
– Abhik Chakraborty, Commented Dec 2, 2014 at 17:47
What is wrong? You need to give more info: the error message would be a good start. — Mad Physicist
– Mad Physicist, Commented Dec 2, 2014 at 17:47

paco666 · Accepted Answer · 2014-12-02 19:07:30Z

2

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 ;

edited Dec 2, 2014 at 19:07

paco666

52 bronze badges

answered Dec 2, 2014 at 17:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paco666 Over a year ago

You haven't erase the line of delete :P Thanks a lot Gordon!!

Collectives™ on Stack Overflow

Create trigger that delete after insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related