CODEWITHSUNDEEP
mysql
Jim
Jim

Reputation: 1357

How to create a trigger using Sequel Pro

I'm trying to create a trigger on a db. I can get this to work fine in SQL fiddle, but when I attempt to create the trigger in Sequal Pro I just get an error.. 

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 '' at line 8

This is the SQL fiddle.. http://sqlfiddle.com/#!2/e849f/1

The procedure I use is to execute the following command in the query window once I've created the schema..

CREATE TRIGGER upd_selectoin 
BEFORE UPDATE ON main 
FOR EACH ROW 
BEGIN
   IF NEW.state = 3 THEN 
   UPDATE selection s JOIN main m 
      ON m.main_id = s.id
      SET s.allow = 1, last_update_timestamp = NOW()
   WHERE s.id = NEW.main_id; 
 END IF;
END;

Upvotes: 2

Views: 5872

Answers (2)

biondo
biondo

Reputation: 161

Try setting a delimiter:

DELIMITER //
CREATE TRIGGER upd_selectoin 
BEFORE UPDATE ON main 
FOR EACH ROW 
BEGIN
   IF NEW.state = 3 THEN 
   UPDATE selection s JOIN main m 
      ON m.main_id = s.id
      SET s.allow = 1, last_update_timestamp = NOW()
   WHERE s.id = NEW.main_id; 
 END IF;
END;
//

Upvotes: 2

Christian Waltjen
Christian Waltjen

Reputation: 459

For anyone having the same issue:

Sequel Pro creates the surrounding statement by itself. So the statement should look like this:

IF NEW.state = 3 THEN 
  UPDATE selection s JOIN main m 
    ON m.main_id = s.id
    SET s.allow = 1, last_update_timestamp = NOW()
  WHERE s.id = NEW.main_id;
END IF;

Upvotes: 6

Related Questions