MYSQL: procedure and trigger error

Question

I'm rather new to MySQL and am encountering an error with my procedure, I want to be able to call the same thing for updates, inserts and deletes to a to my table so need a procedure to call. I've looked at MySQL Fire Trigger for both Insert and Update and The MySQL "DELIMITER" keyword isn't working as well as some other but nothing helped. (the delimiter keyword also doesnt work producing a sepperate error but that doesnt help) The error is this and I cant work out what it means:

"Error SQL query:

-- 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 said:

#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 'CREATE DEFINER=root@localhost PROCEDURE PROC_TRACK_CHANGES BEGIN INSERT INTO ' at line 3"

The errorred code in question is this:

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;

It's probably something simple that i'm missing but the MYSQL debugger confuses me. many thanks!

A trigger drop is a single statement and a trigger create too. Seperate them with `;` — juergen d, Dec 15 '14 at 15:20

score 0 · Answer 1 · answered Dec 15 '14 at 15:19

0

Try this:

DROP PROCEDURE IF EXISTS PROC_TRACK_CHANGES;

Notice the semi-colon at the end.

answered Dec 15 '14 at 15:19

Lee Jenkins

2,299
3
24
39

Thanks, i still get confused on syntax as to what is a single statement and what can span multi lines. – Brandon White Dec 15 '14 at 15:25

score 0 · Accepted Answer · answered Dec 15 '14 at 15:21

0

A procedure drop is a single statement and a procedure create too. Seperate them with the delimiter.

Another thing is that you need to define another delimiter than ;. Otherwise the DB will termiante every statement at the ; which makes some trigger and procedure definitions incomplete. So use for example

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
|

answered Dec 15 '14 at 15:21

juergen d

201,996
37
293
362

The problem with delimiter is that i get the error "ERROR: Unknown Punctuation String" when i use it.. – Brandon White Dec 15 '14 at 15:28
That is odd. How do you execute this? – juergen d Dec 15 '14 at 15:32
I think it came from using multiple character delimiters such as '//' or '$$'... now I'm getting a 'need super privilage error' which i assume means it works, i'm just not allowed to do it. – Brandon White Dec 15 '14 at 15:59

score 0 · Answer 3 · answered Dec 15 '14 at 15:25

You should specify a temporary delimiter other than semi-colon, and use that delimiter at the end of both your DROP PROCEDURE and CREATE PROCEDURE statements.

Also you need to add parentheses after the procedure name in the CREATE PROCEDURE statement even if it takes no arguments.

This should work for you:

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 $$

DELIMITER ;

MYSQL: procedure and trigger error

3 Answers3