MySQL Stored Procedure Programming

Name

PRG-07: Use stored programs to implement code common to multiple triggers

Synopsis

Because you often need to create both an UPDATE and an INSERT trigger to maintain a derived or denormalized column, you might find yourself replicating the same logic in each trigger. For instance, in a previous example we created BEFORE UPDATE and BEFORE INSERT triggers to calculate free shipping and discount rate. If the logic is nontrivial, you should implement the logic in a stored procedure or function and call that routine from your trigger.

Example

Imagine that we are trying to automate the maintenance of a superannuation (18K plan) for our employees. We might create a trigger as follows to automate this processing upon insertion of a new employee row:

    CREATE TRIGGER employees_bu
         BEFORE UPDATE
         ON employees
          FOR EACH ROW
      BEGIN
        DECLARE  v_18k_contrib NUMERIC(4,2);

        IF NEW.salary <20000 THEN
          SET NEW.contrib_18k=0;
        ELSEIF NEW.salary <40000 THEN
          SET NEW.contrib_18k=NEW.salary*.015;
        ELSEIF NEW.salary<55000 THEN
          SET NEW.contrib_18k=NEW.salary*.02;
        ELSE
          SET NEW.contrib_18k=NEW.salary*.025;
        END IF;
      END$$

But we need to ensure that this column is maintained when we create a new employee row. Instead of performing a copy-and-paste into a BEFORE INSERT trigger, we should locate this logic in a stored function as follows:

 CREATE FUNCTION emp18k_contrib(in_salary NUMERIC(10,2)) RETURNS INT DETERMINISTIC BEGIN DECLARE v_contrib NUMERIC(10,2); IF in_salary <20000 THEN SET v_contrib=0; ELSEIF in_salary <40000 ...