- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
希望大家一切都好。
我正在尝试创建一个 mysql 触发器,但是我不断收到以下错误:
[Err] 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' at line 1 [Err] DELIMITER ;
我的代码如下(请注意,实际文件有 130 行长,我刚刚包含了问题所在的部分,或者至少我相信是这样)。
/* BB Events Insert After Trigger */
DELIMITER $$
CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events`
FOR EACH ROW
BEGIN
UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
IF NEW.`status_code` = 62465 THEN
INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`);
UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1) WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
END IF;
IF NEW.`status_code` = 62467 THEN
UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1);
UPDATE `bb_device` SET `current_journey` = '' WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
END IF;
END;$$
DELIMITER ;
/* BB Journey Update After Trigger */
DELIMITER $$
CREATE TRIGGER `journey_update_before` BEFORE UPDATE ON `bb_journey`
FOR EACH ROW
BEGIN
IF NEW.`complete` <> 1 THEN
SET NEW.`distance` = ROUND(NEW.`end_odometer`-NEW.`start_odometer`,2);
SET NEW.`duration` = SEC_TO_TIME(NEW.`end_timestamp`-NEW.`start_timestamp`);
END IF;
IF NEW.`complete` = 1 THEN
UPDATE `bb_events` SET `journey_id` = NEW.`id` WHERE `timestamp` BETWEEN NEW.`start_timestamp` AND NEW.`end_timestamp`;
END IF;
END;$$
DELIMITER ;
非常感谢任何帮助!
谢谢
史密斯:)
最佳答案
我在查看 Mysql create trigger 1064 error 后发现了这一点
正确的代码如下:
/* BB Events Insert After Trigger */
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `event_insert_after` $$
CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events`
FOR EACH ROW
BEGIN
UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
IF NEW.`status_code` = 62465 THEN
INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`);
UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1) WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
END IF;
IF NEW.`status_code` = 62467 THEN
UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1);
UPDATE `bb_device` SET `current_journey` = '' WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
END IF;
END;
$$
/* BB Journey Update After Trigger */
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `journey_update_before` $$
CREATE TRIGGER `journey_update_before` BEFORE UPDATE ON `bb_journey`
FOR EACH ROW
BEGIN
IF NEW.`complete` <> 1 THEN
SET NEW.`distance` = ROUND(NEW.`end_odometer`-NEW.`start_odometer`,2);
SET NEW.`duration` = SEC_TO_TIME(NEW.`end_timestamp`-NEW.`start_timestamp`);
END IF;
IF NEW.`complete` = 1 THEN
UPDATE `bb_events` SET `journey_id` = NEW.`id` WHERE `timestamp` BETWEEN NEW.`start_timestamp` AND NEW.`end_timestamp`;
END IF;
END;
$$
我不知道这是如何运作的,但确实如此。 MySQL 版本 5.5,CentOS 6 64 位。
谢谢大家:-)
关于MySQL - 创建触发器错误 1064( 'DELIMITER ; ' 附近),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/23082874/
我的子查询给出了一个错误:Msg 102, Level 15, State 1, Line 17 Incorrect syntax near ')'. SELECT SalesArea, Branch
当我尝试运行此查询时: select branch_no, max (avg_salary) from (select allocatedto, avg (salary) from sta
所以我正在更新 phpmyadmin 中的表。数据在里面,列已成功创建。当我尝试使用下面的代码添加图像时,出现此错误 Unexpected character. (near "\" at positi
我正在尝试在 phpMyAdmin 中执行以下查询,但出现错误。我已经查看过类似的问题,但我仍然无法弄清楚为什么它不起作用。 INSERT INTO discussion_forum (event_t
我正在用 C 构建一个 client-server socket 模拟 我接受来自客户端的 TCP 连接 然后客户端发送消息到我的服务器。我已成功收到消息,然后遍历 structs 的 array 以
我在 AUTOINCREMENT 附近遇到语法错误。这个错误的原因是什么? CREATE TABLE person ( id INTEGER NOT NULL AUTOINCREMENT,
当我尝试使用 java 在 SQLite 中以编程方式创建数据库时,它会在控制台中生成以下错误。 java.sql.SQLException: near "DATABASE": syntax erro
我似乎无法弄清楚出了什么问题。我尝试查询的子部分,但仍然无法解决问题。 表格格式: poi(id int, minX float, minY float, maxX float, maxY float
我在VHDL中编写了以下代码: library IEEE ; use IEEE.STD_LOGIC_1164.all ; entity encoder is port( x : in std_
我在我的游戏服务器中使用这些文件,每次我添加一个新的玩家模型时,我都会得到 [ERROR] lua/autorun/server/fastdlskins.lua:938: '' expected ne
我正在尝试更新我的 sqlite3 数据库,但出现此错误。我能够成功地将数据插入同一数据库,但无法更新它。请帮忙。 [SQLITE_ERROR] SQL error or missing databa
我想在通知区域旁边显示一个小弹出窗口。它类似于 Outlook/Skype/Live! Messenger/etc 在显示有关新消息的通知时会执行此操作。在我的例子中,它将有一些输入控件(文本框、日期
我正在尝试编写简单的程序以使用 gorp 将行插入表中,但在创建表时出现错误。 代码如下: package main import _ "github.com/mattn/go-sqlite3" im
我正在尝试运行工作目录中的 Octave 文件,但出现错误。 Octave 似乎没有认识到它应该运行该文件。 unknown@unknown> dir .
我正在尝试编写一些代码来模拟具有两个三态缓冲器和VHDL中的上拉电阻的电路。下面是我的代码: library ieee; use ieee.std_logic_1164.all; entity Pul
你能好心告诉我这里出了什么问题吗? conn 是 DriverManager.getConnection(DB_URL) try { PreparedState
我想在go中创建一个事务,同时这样做会出现错误:near "SET": syntax error。代码: db.Exec("SET TRANSACTION ISOLATION LEVEL REPEAT
所以我想用这样的颜色可视化一个矩阵 library(RColorBrewer) vec = rbinom(10000,1,0.1) n = sum(vec) vec = ifelse(vec == 1
private static final String QUERY = "SELECT * FROM " + TABLE_SONG_DETAILS + " WHERE " + TABLE_SONG_D
希望大家一切都好。 我正在尝试创建一个 mysql 触发器,但是我不断收到以下错误: [Err] 1064 - You have an error in your SQL syntax; check
我是一名优秀的程序员,十分优秀!