constraint_trigger.sql

CREATE TABLE order_history (
	order_id INT NOT NULL,
	date_added TIMESTAMPTZ NOT NULL DEFAULT now(),
	status TEXT NOT NULL,

	PRIMARY KEY (order_id, date_added)
);

CREATE TABLE history_allowed_pattern (
	old TEXT NOT NULL,
	new TEXT NOT NULL,

	PRIMARY KEY (old, new)
);

INSERT INTO history_allowed_pattern
	(old, new)
VALUES
	('Pending', 'Processed'),
	('Pending', 'Cancelled'),
	('Processed', 'Shipped'),
	('Shipped', 'Rejected'),
	('Shipped', 'Delivered')
;

CREATE OR REPLACE FUNCTION history_pattern_check() RETURNS TRIGGER AS $$
DECLARE
	allowed BOOL;
	old_status TEXT;
BEGIN
	SELECT
		COALESCE(order_history.status = allowed.status, false) AS allowed,
		order_history.status
	FROM
		(
			SELECT status FROM order_history
			WHERE order_id = NEW.order_id AND date_added != NEW.date_added
			ORDER BY order_history.date_added DESC LIMIT 1) AS order_history
		FULL OUTER JOIN (
			SELECT a.old AS status, a.new FROM history_allowed_pattern AS a
			WHERE a.new = NEW.status) AS allowed USING (status)
	UNION ALL
	SELECT true, null
	LIMIT 1
	INTO allowed, old_status;

	IF NOT allowed THEN
		RAISE EXCEPTION '% is not the next step after %', NEW.status, old_status;
	END IF;

	RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE CONSTRAINT TRIGGER order_history_pattern_check AFTER INSERT ON order_history
	DEFERRABLE INITIALLY DEFERRED
	FOR EACH ROW EXECUTE PROCEDURE history_pattern_check();