mysql trigger on insert not working

Question

I am trying to create a rather simple trigger (or so I thought) on insert but it's not working. Can someone kindly assist me?

CREATE TRIGGER myInsert_Trigger BEFORE UPDATE ON books
FOR EACH ROW
BEGIN
    DECLARE ename VARCHAR(255)
    DECLARE bookid int

    Select bookid=id, ename=b.name From books B inner join authors A on B.AuthorID=A.id Where B.name=new.name

    IF (bookid > 0) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "Book already exists."
    END IF;
END;

I am new to mySql so please go easy on me :)

Mehdi Kashefikia · Accepted Answer · 2011-12-29 06:42:34Z

2

You seem to be writing an Update trigger, which runs before the update takes place. I believe you need to change the first line like this "CREATE TRIGGER myInsert_Trigger AFTER INSERT ON books". This trigger will be fired after all the table constraints are enforced.

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

answered Dec 29, 2011 at 6:42

Mehdi Kashefikia

1011 silver badge4 bronze badges

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

1 Comment

VeBen DoXua Over a year ago

Thanks for pointing out the error. I meant to write an insert trigger and check for certain condition then cancel the insert if the condition doesn't meet the requirement.

Collectives™ on Stack Overflow

mysql trigger on insert not working

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