您的SQL语法有错误;(You have an error in your SQL syntax; creating a trigger in Joomla)

您的SQL语法有错误;(You have an error in your SQL syntax; creating a trigger in Joomla)

我正在使用mysql，我想在Joomla 3.4.8中创建一个触发器。

我有一个在mySQLWorkbench中工作的sql代码：

如果我在Joomla控制器中运行它：

$db = JFactory::getDbo(); $sql = $db->getQuery(true); $sql = 'LOCK TABLES id0ap_virtuemart_products WRITE; DROP TRIGGER IF EXISTS update_vm_products ; DELIMITER // CREATE TRIGGER update_vm_products AFTER UPDATE ON id0ap_virtuemart_products FOR EACH ROW BEGIN INSERT INTO id0ap_virtuemart_notifications VALUES ("", NEW.virtuemart_product_id, "product","update"); END // DELIMITER ; UNLOCK TABLES;'; $db->setQuery($sql); $db->execute();

我明白了：

1064您的SQL语法有错误; 检查与您的MySQL服务器版本对应的手册，以便在'DROP TRIGGER IF EXISTS update_vm_products附近使用正确的语法; DELIMITER //'第2行SQL = LOCK TABLES id0ap_virtuemart_products WRITE; DROP TRIGGER IF EXISTS update_vm_products; DELIMITER //创建TRIGGER update_vm_products更新后id0ap_virtuemart_products每次行开始插入id0ap_virtuemart_notifications值（“”，“NEW.virtuemart_product_id”，“product”，“update”）; END // DELIMITER; 解锁表;

我找不到问题。 但是当我试图解决它时，我发现了一些奇怪的东西。 如果我修改sql删除DELIMITERS和DROP部分：

$sql = 'CREATE TRIGGER update_vm_products AFTER UPDATE ON id0ap_virtuemart_products FOR EACH ROW BEGIN INSERT INTO id0ap_virtuemart_notifications VALUES ("", NEW.virtuemart_product_id, "product","update"); END ;';

是否只是尝试创建它工作的触发器并创建触发器，只有在添加DROP部件时才会失败。 任何想法？ 是否有面向对象的方式来做到这一点？

提前致谢！

I am using mysql and I want to create a trigger in Joomla 3.4.8.

I have a sql code that works in mySQLWorkbench:

If I run it in a Joomla controller:

$db = JFactory::getDbo(); $sql = $db->getQuery(true); $sql = 'LOCK TABLES id0ap_virtuemart_products WRITE; DROP TRIGGER IF EXISTS update_vm_products ; DELIMITER // CREATE TRIGGER update_vm_products AFTER UPDATE ON id0ap_virtuemart_products FOR EACH ROW BEGIN INSERT INTO id0ap_virtuemart_notifications VALUES ("", NEW.virtuemart_product_id, "product","update"); END // DELIMITER ; UNLOCK TABLES;'; $db->setQuery($sql); $db->execute();

I get:

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 'DROP TRIGGER IF EXISTS update_vm_products ; DELIMITER // ' at line 2 SQL=LOCK TABLES id0ap_virtuemart_products WRITE; DROP TRIGGER IF EXISTS update_vm_products ; DELIMITER // CREATE TRIGGER update_vm_products AFTER UPDATE ON id0ap_virtuemart_products FOR EACH ROW BEGIN INSERT INTO id0ap_virtuemart_notifications VALUES ("", NEW.virtuemart_product_id, "product","update"); END // DELIMITER ; UNLOCK TABLES;

I can't find the problem. But I have dicovered something strange while I was trying to solve it. If I modify the sql deleting DELIMITERS and the DROP part:

$sql = 'CREATE TRIGGER update_vm_products AFTER UPDATE ON id0ap_virtuemart_products FOR EACH ROW BEGIN INSERT INTO id0ap_virtuemart_notifications VALUES ("", NEW.virtuemart_product_id, "product","update"); END ;';

whether you just try to create the trigger it works and create the trigger, only fails when you add the DROP part. Any idea? Is there an object-oriented way to do this?

Thanks in advance!

最满意答案

我不是joomla专家，但似乎无论mysql api joomla用于执行sql语句，每次调用只能执行1个sql语句。 尝试自己执行每个sql语句。

I'm not a joomla expert, but it seems that whatever mysql api joomla uses to execute the sql statements, can only execute 1 sql statement per call. Try to execute each sql statement on its own.