我正在使用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.
更多推荐
发布评论