Tuesday, April 18, 2006

MySQL triggers and Master/Detail tables

Have you ever wondered how to update the status of an order to "Closed" when the whole ordered quantity has been received?
If you are on a database that supports triggers like me (I'm on MySQL 5.0.16 right now) you can have something like:

Table structure:

Orders table

  1. DROP TABLE IF EXISTS `test`.`orders`;
  2. CREATE TABLE `test`.`orders` (
  3. `order_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. `order_date` datetime DEFAULT NULL,
  5. `order_status` CHAR(1) NOT NULL DEFAULT '',
  6. PRIMARY KEY (`order_id`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Order lines table

  1. DROP TABLE IF EXISTS `test`.`order_lines`;
  2. CREATE TABLE `test`.`order_lines` (
  3. `order_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  4. `order_line_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  5. `item` varchar(45) NOT NULL DEFAULT '',
  6. `order_quantity` float NOT NULL DEFAULT '0',
  7. PRIMARY KEY (`order_line_id`,`order_id`),
  8. KEY `FK_order_lines_1` (`order_id`),
  9. CONSTRAINT `FK_order_lines_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Order deliveries table

  1. DROP TABLE IF EXISTS `test`.`order_schedule_lines`;
  2. CREATE TABLE `test`.`order_schedule_lines` (
  3. `order_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  4. `order_line_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  5. `schedule_line_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  6. `delivery_quantity` float NOT NULL DEFAULT '0',
  7. PRIMARY KEY (`order_id`,`order_line_id`,`schedule_line_id`),
  8. CONSTRAINT `FK_order_schedule_lines_1` FOREIGN KEY (`order_id`, `order_line_id`) REFERENCES `order_lines` (`order_id`, `order_line_id`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You see, a lot of nice things, InnoDB tables, Foreign Keys ...
But now the real meat, a trigger that, after every insert in the "order_schedule_lines" table, where deliveries for each order line are kept, will compute the difference between ordered quantity and delivered quantity and will update the order status to closed when that difference is 0.

Trigger code:

  1. DELIMITER $$
  2. DROP TRIGGER test.close_order $$
  3. CREATE TRIGGER close_order
  4. AFTER INSERT
  5. ON order_schedule_lines
  6. FOR EACH ROW
  7. BEGIN
  8. DECLARE a float;
  9. SELECT
  10. (ol.order_quantity - COALESCE(SUM(osl.delivery_quantity), 0)) INTO a
  11. FROM orders o
  12. INNER JOIN order_lines ol ON o.order_id = ol.order_id
  13. LEFT OUTER JOIN order_schedule_lines osl ON ol.order_id = osl.order_id AND ol.order_line_id = osl.order_line_id
  14. WHERE osl.order_id = NEW.order_id
  15. GROUP BY
  16. o.order_id;
  17. IF a = 0 THEN
  18. UPDATE orders SET order_status = 'C' WHERE order_id = NEW.order_id;
  19. END IF;
  20. END $$
  21. DELIMITER ;

Of course it would be good to have another check (a before insert trigger? ;-)) that prevents further deliveries on closed orders ...

Partly inspired by this blog entry

No comments: