MySQL TRIGGER syntax, IF THEN INSERT

Question

I want to modify a working trigger that looks like this:

CREATE TRIGGER j_update BEFORE UPDATE ON d
FOR EACH ROW
     INSERT INTO j (
             stampOld, stampNew
     )
     VALUES (
             OLD.stamp, NEW.stamp
     );

to execute only on the success of an IF statement. I can't get the right syntax for this modification.

I'm trying to achieve the following:

 CREATE TRIGGER j_update BEFORE UPDATE ON d
 FOR EACH ROW
     IF (1=1) THEN
         INSERT INTO d (
             stampOld, stampNew
         )
         VALUES (
             OLD.stamp, NEW.stamp
         );                        /*  <--- LINE 21  */
     END IF;

Result: ERROR 1064: Check for right syntax near '' at line 21

Things I've tried:

wrapping the IF ... END IF; in BEGIN .. END;
switching delimiter to $$

I'm using MySQL 5.0.95 for RHEL.

Any help is greatly appreciated.

You should need both those things -- a BEGIN...END and an alternate DELIMITER, and the final END should be terminated with the alt delimiter. — Michael Berkowski
– Michael Berkowski, Commented May 15, 2014 at 16:40

Abhik Chakraborty · Accepted Answer · 2014-05-15 16:42:43Z

2

Try this

delimiter //
CREATE TRIGGER j_update BEFORE UPDATE ON d
FOR EACH ROW
BEGIN
     IF 1=1 THEN
         INSERT INTO d (
             stampOld, stampNew
         )
         VALUES (
             OLD.stamp, NEW.stamp
         );                       
     END IF;
END; //
delimiter ;

Here is one I just tried on my mysql but its for update almost same with your logic

mysql> delimiter //
mysql> create trigger tbl_two_ins 
    -> after insert on tabletwo 
    -> for each row
    -> begin  
    ->   if 1=1 then
    ->     update tableone t1 set t1.tb2_id = new.tab2_id where t1.tb1_id = new.tb1_id ; 
    ->   end if ;
    -> end ; //
Query OK, 0 rows affected (0.56 sec)

mysql> delimiter ;

answered May 15, 2014 at 16:42

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

Collectives™ on Stack Overflow

MySQL TRIGGER syntax, IF THEN INSERT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related