如何在Codeigniter的迁移库中创建触发器 [英] How to create triggers in Codeigniter's migration library
问题描述
触发器的创建无法正常工作,例如,我尝试了所有可以想到的事情:
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 ;");
无论我做什么,我都觉得它不会创建触发器,因为Codeigniter仅在一行中创建SQL语句.尝试使用和不使用换行符,仍然会收到此消息:
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:
错误号: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有效
In PHPMyAdmin, the trigger create works by the way, so the SQL is valid
推荐答案
通过PHP执行时,应从触发器中删除定界符. mysql_或mysqli_函数应该能够执行触发器,而无需 定界符.
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.
这是方法.
$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
");
这篇关于如何在Codeigniter的迁移库中创建触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!