mySQL Trigger returns error

Question

I am trying to set up a trigger on a table to copy the contents of a row into another table.

I have the following:

CREATE TRIGGER story_deleted BEFORE DELETE ON stories 
  FOR EACH ROW BEGIN
    INSERT INTO stories_backup SET story_id = OLD.story_id;  
  END;

This returns the following error though:

1064 - 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 3

I can't work out where I'm going wrong with this. Any ideas?

The code is correct. How are you executing this query, in phpmyadmin? — Devart
– Devart, Commented Apr 13, 2012 at 12:45
It can be a problem with source objects and delimiters. Try workaround. — Devart
– Devart, Commented Apr 13, 2012 at 12:49
@Devart This returns the following: 1419 - You do not have the SUPER privilege and binary logging is enabled — Tom
– Tom, Commented Apr 13, 2012 at 12:50

RolandoMySQLDBA · Accepted Answer · 2012-04-13 18:26:30Z

2

Try changing the delimiter

DELIMITER $$

CREATE TRIGGER story_deleted BEFORE DELETE ON stories  
  FOR EACH ROW
  BEGIN 
    INSERT INTO stories_backup SET story_id = OLD.story_id;   
  END $$

DELIMITER ;

and as far as your privileges go, run this query

SHOW GRANTS;

If SUPER is not there, you could

request your DBA to add that privilege for you
have your DBA create the trigger for you

edited Apr 13, 2012 at 18:26

answered Apr 13, 2012 at 18:17

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

mySQL Trigger returns error

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