如何在 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 中，触发器 create 顺便工作，所以 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屋！