order status count triggers

gistfile1.sql

CREATE TRIGGER ai_order_statuses AFTER INSERT ON orders
FOR EACH ROW UPDATE order_status_counts
             SET total = total + 1
             WHERE status = NEW.status
             AND order_type = NEW.type
             AND (NEW.on_hold != 1 OR NEW.on_hold IS NULL)



CREATE TRIGGER ad_order_statuses AFTER DELETE ON orders
FOR EACH ROW UPDATE order_status_counts
             SET total = total - 1
             WHERE status = OLD.status
             AND order_type = OLD.type
             AND (OLD.on_hold != 1 OR OLD.on_hold IS NULL)



CREATE TRIGGER au_order_statuses AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF (OLD.status <> NEW.status)
    THEN
        UPDATE order_status_counts
        SET total = total + IF(status = NEW.status, 1, -1)
        WHERE status IN (OLD.status, NEW.status)
        AND order_type = OLD.type
        AND (OLD.on_hold != 1 OR OLD.on_hold IS NULL);
    END IF;
END
;