MYSQL:过程和触发器错误

Question

我对 MySQL 相当陌生，我的过程遇到错误，我希望能够调用相同的方法来更新、插入和删除我的表，因此需要一个过程来调用。我看过MySQL Fire Trigger for both Insert and Update和 The MySQL "DELIMITER" keyword isn't working以及其他一些但没有任何帮助。 (分隔符关键字也不起作用，产生单独的错误，但这没有帮助)错误是这样的，我无法弄清楚它的含义:

“错误SQL查询:

-- trigger on work log change
DROP PROCEDURE IF EXISTS PROC_TRACK_CHANGES CREATE DEFINER = root@localhost PROCEDURE PROC_TRACK_CHANGES BEGIN INSERT INTO CHANGES( LoggedTimeStamp, EmployeeID, LogTimeStamp, ProposalID, WorkDone ) 
SELECT NOW( ) , EmployeeID, LogTimeStamp, ProposalID, WorkDone
FROM WORKLOG;

MySQL 说:

#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'CREATE DEFINER=root@localhost PROCEDURE PROC_TRACK_CHANGES 附近使用的正确语法开始 INSERT INTO ' 在第 3 行”

<小时/>

有问题的错误代码是这样的:

CREATE TABLE WORKLOG(
EmployeeID              BIGINT UNSIGNED NOT NULL UNIQUE, -- FKEY PKEY
LogTimeStamp            TIMESTAMP, -- PKEY
ProposalID              BIGINT UNSIGNED NOT NULL UNIQUE, -- FKEY
WorkDone                VARCHAR(200),
CONSTRAINT WORKLOG_FOREIGN_KEY_EMP FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEES (EmployeeID),
CONSTRAINT WORKLOG_FOREIGN_KEY_PRO FOREIGN KEY (ProposalID) REFERENCES PROPOSALS (ProposalID),
CONSTRAINT WORKLOG_PROPOSALS_PRIMARY_KEY PRIMARY KEY (EmployeeID, LogTimeStamp)
);

CREATE TABLE CHANGES(
LoggedTimeStamp         TIMESTAMP, -- PKEY
EmployeeID              BIGINT UNSIGNED NOT NULL UNIQUE, -- FKEY
LogTimeStamp            TIMESTAMP, -- fkey
ProposalID              BIGINT UNSIGNED NOT NULL UNIQUE, -- FKEY
WorkDone                VARCHAR(200),
CONSTRAINT CHANGES_FOREIGN_KEY_EMP FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEES (EmployeeID),
CONSTRAINT CHANGES_FOREIGN_KEY_PRO FOREIGN KEY (ProposalID) REFERENCES PROPOSALS (ProposalID),
CONSTRAINT CHANGES_PRIMARY_KEY PRIMARY KEY (LoggedTimeStamp)
);

-- trigger on work log change
DROP PROCEDURE IF EXISTS PROC_TRACK_CHANGES
CREATE DEFINER=root@localhost PROCEDURE PROC_TRACK_CHANGES
BEGIN
    INSERT INTO CHANGES (LoggedTimeStamp, EmployeeID, LogTimeStamp, ProposalID, WorkDone)
    SELECT now(), EmployeeID, LogTimeStamp, ProposalID, WorkDone FROM WORKLOG;
END;

DROP TRIGGER IF EXISTS TR_WORKLOG_UPDATE
CREATE DEFINER=root@localhost TRIGGER TR_WORKLOG_UPDATE
    BEFORE UPDATE ON 'WORKLOG'
    FOR EACH ROW
BEGIN
    CALL PROC_TRACK_CHANGES();
END;

DROP TRIGGER IF EXISTS TR_WORKLOG_INSERT
CREATE DEFINER=root@localhost TRIGGER TR_WORKLOG_INSERT BEFORE INSERT ON WORKLOG
FOR EACH ROW
BEGIN
    CALL PROC_TRACK_CHANGES();
END;

DROP TRIGGER IF EXISTS TR_WORKLOG_DELETE
CREATE DEFINER=root@localhost TRIGGER TR_WORKLOG_DELETE BEFORE DELETE ON WORKLOG
FOR EACH ROW
BEGIN
    CALL PROC_TRACK_CHANGES();
END;

这可能是我错过的一些简单的东西，但 MYSQL 调试器让我感到困惑。非常感谢!

Answer 1

过程drop是一个单独的语句，也是一个过程create。用分隔符分隔它们。

另一件事是您需要定义另一个分隔符而不是;。否则，数据库将终止 ; 处的每个语句，这会使某些触发器和过程定义不完整。因此使用示例

delimiter |
DROP PROCEDURE IF EXISTS PROC_TRACK_CHANGES
|
CREATE DEFINER=root@localhost PROCEDURE PROC_TRACK_CHANGES
BEGIN
    INSERT INTO CHANGES (LoggedTimeStamp, EmployeeID, LogTimeStamp, ProposalID, WorkDone)
    SELECT now(), EmployeeID, LogTimeStamp, ProposalID, WorkDone FROM WORKLOG;
END
|