CODEWITHSUNDEEP
mysql
phaworth
phaworth

Reputation: 447

How to create a BEFORE INSERT Trigger

I'm trying to create a BEFORE INSERT trigger and getting errors that I don't understand.

The MySQL to create the trigger looks like this:

CREATE  TRIGGER `GetPolyLinkID` BEFORE INSERT ON `TableA` 
FOR EACH ROW 
BEGIN
    INSERT INTO TableB () VALUES ();
    NEW.PolyLinkID = last_insert_id();
END

The error I'm getting is

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

Upvotes: 0

Views: 147

Answers (1)

EchoMike444
EchoMike444

Reputation: 1692

You forgot SET ... begore NEW.PolyLinkID = last_insert_id();

And

For creating complex trigger , you must use the delimiter keyword .

This is a example from rom https://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html .

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
   IF NEW.amount < 0 THEN
       SET NEW.amount = 0;
   ELSEIF NEW.amount > 100 THEN
        SET NEW.amount = 100;
   END IF;
END;
//
delimiter ;

Screen shot with sequel pro

example with SEQUEL PRO

Upvotes: 1

Related Questions