CODEWITHSUNDEEP
mysqlsqltriggers
Jay
Jay

Reputation: 89

Mysql Trigger with condition and calculations

Im using mysql trigger to insert contribution from After insert task table i need to calculate total hours (total_hours = new.hours + new. overtime) and i need to select total_hours from contribution table

This is mysql trigger im using but its not working 

     CREATE TRIGGER update_hours AFTER INSERT ON tasks
     FOR EACH ROW 
     BEGIN
     SELECT p_id d_id hours overtime total_hours FROM contribution WHERE 
      p_id == new.p_id && d_id == new.d_id 

     new.total_hours = total_hours + new.hours + new.overtime


 INSERT INTO contribution
      ( p_id,
       d_id,
       hours,
       overtime,
       total_hours,
       contribution )
       VALUES
          ( NEW.p_id,
            NEW.d_id,
            NEW.hours,
            NEW.overtime,
            new.total_hours,
            NEW.contribution );
      END IF;

 END

i need to get values form contribution table and add there values to hours and overtime

I need to insert to the contribution table when add a task hours and overtime. need to calculate total hours (hours + overtime) and add to contribution total_hours .

Upvotes: 0

Views: 447

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

Step one is to get a total of previous entries in contribution. Step two is to add the new values inserted into tasks, and add to the total. Step three is to insert the results into contribution.

DELIMITER |

CREATE TRIGGER update_hours AFTER INSERT ON tasks
FOR EACH ROW 
BEGIN
    SET @old_total_hours = (SELECT SUM(hours + overtime)
        FROM contribution 
        WHERE p_id == new.p_id && d_id == new.d_id 
        GROUP BY p_id);

    SET @total_hours = @old_total_hours + new.hours + new.overtime;

    INSERT INTO contribution
    ( p_id,
    d_id,
    hours,
    overtime,
    total_hours,
    contribution )
    VALUES
    ( 
        NEW.p_id,
        NEW.d_id,
        NEW.hours,
        NEW.overtime,
        @total_hours,
        NEW.contribution
    );
END|

DELIMITER ;

Upvotes: 1

Related Questions