5

Triggers creation are just not working, I tried everything I can think of, for instance, like that:

$this->db->query("DELIMITER //\r\n
CREATE TRIGGER `delete_post` BEFORE DELETE ON `posts`\r\n
FOR EACH ROW BEGIN\r\n
DELETE FROM page_content WHERE page_content.post_id = OLD.post_id;\r\n
END\r\n
//\r\n
DELIMITER ;");

And whatever I do, I have the feeling it won't create the trigger because Codeigniter create the SQL statement in only one line. Tried with and without the line breaks, still get this message:

Error Number: 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 'DELIMITER // CREATE TRIGGER `delete_post` BEFORE DELETE ON `posts` FOR EAC' at line 1

DELIMITER // CREATE TRIGGER `delete_post` BEFORE DELETE ON `posts` FOR EACH ROW BEGIN DELETE FROM page_content WHERE page_content.post_id = OLD.post_id; END // DELIMITER ;

In PHPMyAdmin, the trigger create works by the way, so the SQL is valid

NaturalBornCamper
  • 3,675
  • 5
  • 39
  • 58
  • takeout the delimiter part , mysql_ or mysqli_ functions should be able to execute the trigger without the delimiter. – Abhik Chakraborty May 20 '14 at 03:33
  • I'll try it, because it is definitely a delimiter problem, it needs to be on its own line in my tests – NaturalBornCamper May 20 '14 at 03:34
  • yes give it a try you may also check this thread http://stackoverflow.com/questions/3762880/create-mysql-trigger-via-php – Abhik Chakraborty May 20 '14 at 03:36
  • Yep, you guys were right, I was searching google for codeigniter instead of php, since it was using the query method. It works now without the delimiter. You should copy-paste my code without the delimiters so I can accept your solution – NaturalBornCamper May 20 '14 at 03:39

2 Answers2

7

You should remove the delimiter from the trigger while executing via PHP. mysql_ or mysqli_ functions should be able to execute the trigger without the delimiter.

Here is how to do it.

$this->db->query("
CREATE TRIGGER `delete_post` BEFORE DELETE ON `posts`\r\n
FOR EACH ROW BEGIN\r\n
DELETE FROM page_content WHERE page_content.post_id = OLD.post_id;\r\n
END\r\n
//\r\n
");
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
0

This is the simplest way to fix your issue.

$this->db->query("CREATE TRIGGER `delete_post` BEFORE DELETE ON `posts` ".
"FOR EACH ROW BEGIN ".
"DELETE FROM page_content WHERE page_content.post_id = OLD.post_id; ".
"END ");

Just don't forget to put a single space just before the closing double quotes at every line.

Alpha Tango
  • 1
  • 1