CODEWITHSUNDEEP
mysqltriggers
VeBen DoXua
VeBen DoXua

Reputation: 11

mysql trigger on insert not working

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 :)

Upvotes: 1

Views: 496

Answers (1)

Mehdi Kashefikia
Mehdi Kashefikia

Reputation: 101

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

Upvotes: 2

Related Questions