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

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

Order lines table

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

Order deliveries table

DROP TABLE IF EXISTS `test`.`order_schedule_lines`;
CREATE TABLE  `test`.`order_schedule_lines` (
  `order_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `order_line_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `schedule_line_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `delivery_quantity` float NOT NULL DEFAULT '0',
  PRIMARY KEY  (`order_id`,`order_line_id`,`schedule_line_id`),
  CONSTRAINT `FK_order_schedule_lines_1` FOREIGN KEY (`order_id`, `order_line_id`) REFERENCES `order_lines` (`order_id`, `order_line_id`)
) 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:

DELIMITER $$
 
DROP TRIGGER test.close_order $$
 
CREATE TRIGGER close_order
AFTER INSERT
ON order_schedule_lines
 
 
 
  FOR EACH ROW
 
 
 
  BEGIN
 
  DECLARE a float;
 
SELECT
(ol.order_quantity - COALESCE(SUM(osl.delivery_quantity), 0))  INTO a
FROM orders o
INNER JOIN order_lines ol ON o.order_id = ol.order_id
LEFT OUTER JOIN order_schedule_lines osl ON ol.order_id = osl.order_id AND ol.order_line_id = osl.order_line_id
WHERE osl.order_id = NEW.order_id
GROUP BY
o.order_id;
 
IF a = 0 THEN
 
UPDATE orders SET order_status = 'C' WHERE order_id = NEW.order_id;
 
END IF;
 
  END $$
 
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