我使用以下 PHP 代码来创建触发器，但总是出现错误，请帮我解决它
我使用以下 PHP 代码来创建触发器，但总是出现错误，请帮我解决它。
$link = mysql_connect('localhost','root','rainserver');
mysql_select_db('information_schema');
echo $trgquery = "DELIMITER $$ DROP TRIGGER `update_data` $$ CREATE TRIGGER `update_data` AFTER UPDATE on `jos_menu` FOR EACH ROW BEGIN
IF (NEW.menutype != OLD.menutype) THEN
INSERT INTO jos_menuaudit set menuid=OLD.id, oldvalue = OLD.menutype, newvalue = NEW.menutype, field = 'menutype';
END IF;
IF (NEW.name != OLD.name) THEN
INSERT INTO jos_menuaudit set menuid=OLD.id, oldvalue = OLD.name,
newvalue = NEW.name, field = 'name';
END IF;
IF (NEW.alias != OLD.alias) THEN
INSERT INTO jos_menuaudit set menuid=OLD.id, oldvalue = OLD.alias,
newvalue = NEW.alias, field = 'alias';
END IF;
END$$ DELIMITER ;";
echo "<br>";
//$trig = mysqli_query($link,$trgquery) or die("Error Exist".mysqli_error($link));
$trig = mysql_query($trgquery) or die("Error Exist".mysql_error());
我收到的错误为：
Error ExistYou 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 `update_data` $$ CREATE TRIGGER `update_data` AFTER UPDATE on `j' at line 1
请帮助我创建触发器...
I am using following PHP code for trigger creation but always get error, please help me to resolve it.
$link = mysql_connect('localhost','root','rainserver');
mysql_select_db('information_schema');
echo $trgquery = "DELIMITER $ DROP TRIGGER `update_data` $ CREATE TRIGGER `update_data` AFTER UPDATE on `jos_menu` FOR EACH ROW BEGIN
IF (NEW.menutype != OLD.menutype) THEN
INSERT INTO jos_menuaudit set menuid=OLD.id, oldvalue = OLD.menutype, newvalue = NEW.menutype, field = 'menutype';
END IF;
IF (NEW.name != OLD.name) THEN
INSERT INTO jos_menuaudit set menuid=OLD.id, oldvalue = OLD.name,
newvalue = NEW.name, field = 'name';
END IF;
IF (NEW.alias != OLD.alias) THEN
INSERT INTO jos_menuaudit set menuid=OLD.id, oldvalue = OLD.alias,
newvalue = NEW.alias, field = 'alias';
END IF;
END$ DELIMITER ;";
echo "<br>";
//$trig = mysqli_query($link,$trgquery) or die("Error Exist".mysqli_error($link));
$trig = mysql_query($trgquery) or die("Error Exist".mysql_error());
I get the error as:
Error ExistYou 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 `update_data` $ CREATE TRIGGER `update_data` AFTER UPDATE on `j' at line 1
PLease help me to create my trigger...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
该错误准确地告诉您出了什么问题。
DELIMITER $$ DROP ...不是有效的语句。部分原因是delimiter是 MySQL 命令，而不是语句。另一个原因是“DELIMITER $$”中的“$$”是命令的一部分，因此不会终止命令。您仍然需要包含旧的分隔符。根据文档，
mysql_query不支持多个语句，因此您必须为每个语句发出单独的查询。更好的是，切换到较新的 mysqli 驱动程序，其multi_query< /code>函数适用于单个查询中的多个语句。 mysqli 还支持准备好的语句，这在您的示例中并不重要，但在大多数其他情况下却很重要。 PDO 甚至更新，并且还支持 准备好的语句，我相信 PDO MySQL 驱动程序支持多查询。作为触发器的替代方案，基于行的二进制日志记录 将记录对数据库中的表所做的更改。
The error tells you exactly what's wrong.
DELIMITER $$ DROP ...isn't a valid statement. Part of the reason for this isdelimiteris a MySQL command, not a statement. Another reason is the "$$" in "DELIMITER $$" is part of the command and thus doesn't terminate the command. You still need to include the old delimiter.According to the documentation,
mysql_querydoesn't support multiple statements, so you'll have to issue separate queries for each one. Better yet, switch to the newer mysqli driver, whosemulti_queryfunction works for multiple statements in a single query. mysqli also supports prepared statements, which doesn't matter so much in your example, but does in most other situations. PDO is even newer and also supports prepared statements, and I believe the PDO MySQL driver supports multi-queries.As an alternative to triggers, row-based binary logging will record changes made to tables in a database.