Example trigger for ensuring that history states are inserted in the proper order. Ever needed to make sure that an order is filled before it gets shipped? This is how you would do it in the database.

trigger.sql

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

	UNIQUE (old, new)
);

INSERT INTO history_allowed_pattern
	(old, new)
VALUES
	('Pending', 'Submitted'),
	('Pending', 'Cancelled'),
	('Submitted', 'Approved'),
	('Submitted', 'Cancelled'),
	('Approved', 'Cancelled')
;

--------------------------------------------------------------------- |

CREATE TABLE history (
	id INT NOT NULL,
	status TEXT NOT NULL,
	date_added TIMESTAMPTZ NOT NULL DEFAULT now(),

	PRIMARY KEY (id, date_added)
);

--------------------------------------------------------------------- |

CREATE OR REPLACE FUNCTION history_pattern_check() RETURNS TRIGGER AS $$
BEGIN
	IF NOT (
		SELECT
			COALESCE(history.status = allowed.status, false) AS allowed
		FROM
			(SELECT status FROM history WHERE id = NEW.id AND date_added < NEW.date_added ORDER BY history.date_added DESC LIMIT 1) AS 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
		LIMIT 1
		) THEN
		RAISE EXCEPTION 'new row for relation "history" violates check constraint "history_allowed_pattern_check"' USING ERRCODE = 'check_violation';
	END IF;

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

CREATE CONSTRAINT TRIGGER history_pattern_check AFTER INSERT ON history
	FOR EACH ROW EXECUTE PROCEDURE history_pattern_check();

--------------------------------------------------------------------- |

-- this should succeed
INSERT INTO history
	(id, status, date_added)
VALUES
	(1, 'Pending', now() - interval '5 hours'),
	(1, 'Submitted', now() - interval '4 hours'),
	(1, 'Approved', now() - interval '3 hours')
;

-- this should fail
INSERT INTO history
	(id, status, date_added)
VALUES
	(2, 'Pending', now() - interval '5 hours'),
	(2, 'Approved', now() - interval '3 hours')
;