-1

Can anyone tell me what is wrong with this mysql trigger? I am unable to understand what is actually going wrong.

CREATE TRIGGER `task_completion` after UPDATE
ON `inhouse_session_tasks` FOR each row 
   BEGIN

      SET @total = (
                    SELECT
                           COUNT(ist.id) 
                    FROM
                          `inhouse_session_tasks` ist 
                    LEFT JOIN
                          `inhouse_sessions` iss 
                          ON iss.id = NEW.session_id 
                    LEFT JOIN
                          `inhouse_batches` ib 
                          ON ib.id = iss.batch_id 
                    LEFT JOIN
                          `inhouse_process` ip 
                          ON ip.id = ib.process_id 
                    WHERE
                          ip.id = ib.process_id
                   );

    SET @completed = (
                      SELECT
                              COUNT(ist.id) 
                      FROM
                              `inhouse_session_tasks` ist 
                      LEFT JOIN
                              `inhouse_sessions` iss 
                              ON iss.id = NEW.session_id 
                      LEFT JOIN
                              `inhouse_batches` ib 
                              ON ib.id = iss.batch_id 
                      LEFT JOIN
                              `inhouse_process` ip 
                              ON ip.id = ib.process_id 
                      WHERE
                              ip.id = ib.process_id 
                              AND ist.status = 1
                     );

    set @average = (
                    @completed *100 / @total
                   );

    update
           `inhouse_process` 
    SET
           `process` = @average 
    WHERE
           1;

Error:

The following query has failed: "CREATE TRIGGER task_completion AFTER UPDATE ON inhouse_session_tasks FOR EACH ROW CREATE TRIGGER task_completion after UPDATE ON inhouse_session_tasks FOR each row BEGIN SET @total = ( SELECT COUNT(ist.id) FROM inhouse_session_tasks ist LEFT JOIN inhouse_sessions iss ON iss.id = NEW.session_id LEFT JOIN inhouse_batches ib ON ib.id = iss.batch_id LEFT JOIN inhouse_process ip ON ip.id = ib.process_id WHERE ip.id = ib.process_id ); SET @completed = ( SELECT COUNT(ist.id) FROM inhouse_session_tasks ist LEFT JOIN inhouse_sessions iss ON iss.id = NEW.session_id LEFT JOIN inhouse_batches ib ON ib.id = iss.batch_id LEFT JOIN inhouse_process ip ON ip.id = ib.process_id WHERE ip.id = ib.process_id AND ist.status = 1 ); set @average = ( @completed *100 / @total ); update inhouse_process SET process = @average WHERE 1;"

MySQL said: #1303 - Can't create a TRIGGER from within another stored routine

  • 1
    What statement terminator are you using? As a side note, you can optimize @total and @completed calculation as COUNT(ist.id) as total, COUNT(case when ist.status = 1 then 1 end) as completed – Lennart Feb 23 '18 at 8:29
  • @Lennart i want both of them to get how much process is completed. – Ashish Feb 23 '18 at 8:57
  • Does inhouse_process table have a single row? – ypercubeᵀᴹ Feb 23 '18 at 11:53
  • @Ashish Patel, you can determine both values at the same time. In fact, you can do the update without a single variable. – Lennart Feb 23 '18 at 12:43
2

Since the real problem is solved I'll just add a simplified body:

DELIMITER //
CREATE TRIGGER task_completion 
AFTER UPDATE ON inhouse_session_tasks 
FOR each row 
BEGIN
    UPDATE inhouse_process 
        SET process = ( 
           SELECT 100*COUNT(case when ist.status = 1 then 1 end) / COUNT(1)
           FROM inhouse_session_tasks ist 
           LEFT JOIN inhouse_sessions iss 
               ON iss.id = NEW.session_id 
           LEFT JOIN inhouse_batches ib 
               ON ib.id = iss.batch_id 
           LEFT JOIN inhouse_process ip 
               ON ip.id = ib.process_id 
           WHERE ip.id = ib.process_id 
       )
    WHERE ...;
END //
DELIMITER ;
1

There is a missing END to the end of your query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.