触发器创建就是不工作，我尝试了所有我能想到的，例如，像这样:
$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 ;");
无论我做什么，我都感觉它不会创建触发器，因为Codeigniter只在一行中创建SQL语句。使用和不使用换行符，仍然得到这个消息:
错误编号: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 ;
在PHPMyAdmin中，触发器创建顺便工作，所以SQL是有效的
您应该在通过PHP执行时从触发器中删除分隔符。Mysql_或mysqli_函数应该能够在没有分隔符。
方法如下。
$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
");
这是解决问题最简单的方法。
$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 ");
只是不要忘记在每行结束双引号前加一个空格。