Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm attempting to write my own mini-rollback procedure. I have a table that tracks any updates or deletes to another table using a trigger. I am attempting to make it possible to restore one or more of these tracked changes through the use of a procedure. However, I'm receiving a syntax error with the following:

-- UNDO_CHANGES_PROCEDURE - This copies the values of the work log track table back into the relevant record in the work log table if an accidental edit or deletion is made.
DROP PROCEDURE IF EXISTS UNDO_CHANGES_PROCEDURE;

DELIMITER $$

CREATE PROCEDURE UNDO_CHANGES_PROCEDURE(ID INT(6))
BEGIN
DECLARE var_trig CHAR(8);

SET var_trig = (SELECT TRIGGER_TYPE FROM WORK_LOG_TRACK WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID);

IF var_trig = 'Update' THEN
UPDATE WORK_LOG SET ENTRY_NUMBER = WORK_LOG_TRACK.ENTRY_NUMBER_FK, EMPLOYEE_ID_FK = WORK_LOG_TRACK.EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK = WORK_LOG_TRACK.WORK_ORDER_NUMBER_FK, ENTRY_TIME = WORK_LOG_TRACK.ENTRY_TIME, WORK_DONE = WORK_LOG_TRACK.WORK_DONE WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID AND WORK_LOG.ENTRY_NUMBER = WORK_LOG_TRACK.ENTRY_NUMBER_FK;
ELSE
INSERT INTO WORK_LOG(ENTRY_NUMBER, EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK, ENTRY_TIME, WORK_DONE) VALUES (WORK_LOG_TRACK.ENTRY_NUMBER_FK, WORK_LOG_TRACK.EMPLOYEE_ID_FK, WORK_LOG_TRACK.WORK_ORDER_NUMBER_FK, WORK_LOG_TRACK.ENTRY_TIME, WORK_LOG_TRACK.WORK_DONE) WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID;
END IF;
END;
$$

DELIMITER ;

The syntax error comes in in regards to my update statement, any help or suggestions would be appreciated.

share|improve this question

migrated from stackoverflow.com Dec 7 '12 at 15:40

This question came from our site for professional and enthusiast programmers.

    
could you add the error message? –  miracle173 Dec 7 '12 at 16:30

1 Answer 1

The syntax error comes from the INSERT statement. The statement INSERT...VALUES does not support WHERE clause.

Possible you want this -

INSERT INTO WORK_LOG (
  ENTRY_NUMBER, EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK, ENTRY_TIME, WORK_DONE
)
SELECT
  ENTRY_NUMBER_FK,
  EMPLOYEE_ID_FK,
  WORK_ORDER_NUMBER_FK,
  ENTRY_TIME,
  WORK_DONE
FROM
  WORK_LOG_TRACK
WHERE
    WORK_LOG_EDIT_NUMBER = ID;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.