CODEWITHSUNDEEP
mysql
Huỳnh Đình An
Huỳnh Đình An

Reputation: 11

Cannot create trigger on MySQL

enter code hereCREATE 
/*!50017 DEFINER = 'root'@'localhost' */ 
TRIGGER `update_bug_question_number_after_insert` AFTER INSERT ON `work_item` 
FOR EACH ROW BEGIN 
    SET @m_last_number := 0; 
IF (NEW.type = 1) THEN 
    BEGIN 
        SELECT last_bug_number FROM work_item WHERE id = NEW.parent_work_item_id INTO @m_last_number; 
        IF @m_last_number IS NULL THEN 
            SET @m_last_number = 0; 
        /* inscrease last bug number */ 
        SET @m_last_number = @m_last_number + 1; 
        /* update last bug number parent work item*/ 
        UPDATE work_item SET last_bug_number = @m_last_number WHERE id = NEW.parent_work_item_id; 
        /* update bug number current work item*/ 
        UPDATE work_item SET bug_number = @m_last_number WHERE id = NEW.id; 
    END 
ELSE IF (NEW.type - 4) THEN 
    BEGIN 
        SELECT last_question_number FROM work_item WHERE id = NEW.parent_work_item_id INTO @m_last_number; 
        IF @m_last_number IS NULL THEN 
            SET @m_last_number = 0; 
        /* inscrease last bug number */ 
        SET @m_last_number = @m_last_number + 1; 
        /* update last bug number parent work item*/ 
        UPDATE work_item SET last_question_number = @m_last_number WHERE id = NEW.parent_work_item_id; 
        /* update bug number current work item*/ 
        UPDATE work_item SET question_number = @m_last_number WHERE id = NEW.id; 
    END 
END;

Messages show: 

12 queries executed, 2 success, 10 errors, 0 warnings

Query: CREATE 0017 DEFINER = 'root'@'localhost' */ TRIGGER update_bug_question_number_after_insert AFTER INSERT ON work_item FOR EA...

Error Code: 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 '' at line 5

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: if (NEW.type = 1) then begin select last_bug_number from work_item where id = NEW.parent_work_item_id into @m_last_number

Error Code: 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 'if (NEW.type = 1) then begin select last_bug_number from work_it' at line 1

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: if @m_last_number is null then SET @m_last_number = 0

Error Code: 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 'if @m_last_number is null then SET @m_last_number = 0' at line 1

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: SET @m_last_number = @m_last_number + 1

0 row(s) affected

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: Update work_item set last_bug_number = @m_last_number where id = NEW.parent_work_item_id

Error Code: 1054 Unknown column 'NEW.parent_work_item_id' in 'where clause'

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: UPDATE work_item SET bug_number = @m_last_number WHERE id = NEW.id

Error Code: 1054 Unknown column 'NEW.id' in 'where clause'

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: end ELSE IF (NEW.type - 4) then Begin SELECT last_question_number FROM work_item WHERE id = NEW.parent_work_item_id INTO @m_last...

Error Code: 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 'end ELSE IF (NEW.type - 4) then Begin SELECT last_question_num' at line 1

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: IF @m_last_number IS NULL THEN SET @m_last_number = 0

Error Code: 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 'IF @m_last_number IS NULL THEN SET @m_last_number = 0' at line 1

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: SET @m_last_number = @m_last_number + 1

0 row(s) affected

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: UPDATE work_item SET last_question_number = @m_last_number WHERE id = NEW.parent_work_item_id

Error Code: 1054 Unknown column 'NEW.parent_work_item_id' in 'where clause'

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: UPDATE work_item SET question_number = @m_last_number WHERE id = NEW.id

Error Code: 1054 Unknown column 'NEW.id' in 'where clause'

Execution Time : 0 sec Transfer Time : 0 sec

Total Time : 0 sec

Query: end END

Error Code: 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 'end END' at line 1

Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0 sec

Upvotes: 1

Views: 252

Answers (1)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

try this way:

DELIMITER $$
/*!50017 DEFINER = 'root'@'localhost' */ 
create TRIGGER `update_bug_question_number_after_insert` AFTER INSERT ON `work_item` FOR EACH ROW 

BEGIN 
    SET @m_last_number := 0; 
IF (NEW.type = 1) THEN 

        SELECT last_bug_number FROM work_item WHERE id = NEW.parent_work_item_id INTO @m_last_number; 
        IF @m_last_number IS NULL THEN 
            SET @m_last_number = 0; 
        /* inscrease last bug number */ 
        SET @m_last_number = @m_last_number + 1; 
        /* update last bug number parent work item*/ 
        UPDATE work_item SET last_bug_number = @m_last_number WHERE id = NEW.parent_work_item_id; 
        /* update bug number current work item*/ 
        UPDATE work_item SET bug_number = @m_last_number WHERE id = NEW.id; 
  END IF;
ELSEIF  (NEW.type - 4) THEN 

        SELECT last_question_number FROM work_item WHERE id = NEW.parent_work_item_id INTO @m_last_number; 
        IF @m_last_number IS NULL THEN 
            SET @m_last_number = 0; 
        /* inscrease last bug number */ 
        SET @m_last_number = @m_last_number + 1; 
        /* update last bug number parent work item*/ 
        UPDATE work_item SET last_question_number = @m_last_number WHERE id = NEW.parent_work_item_id; 
        /* update bug number current work item*/ 
        UPDATE work_item SET question_number = @m_last_number WHERE id = NEW.id; 


      END IF;
  END IF;
END;
$$
DELIMITER ;

Upvotes: 0

Related Questions