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 ...
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.