|
|
EXAMPLE SQL SNIPPET
The article (Keet, 2026-05-28) does not transcribe its generated SQL; it only states the rule: "the start of tenderer must be before the start of winner, else that organisation won't be inserted in the winner table." The snippet below illustrates that exact rule in both forms discussed in the description.
-- (1) STATELESS TRANSITION TABLE -- the allowed edges of the state machine,
-- populated from the binary fact type "transition (from-state) to (to-state)".
CREATE TABLE allowed_transition (
from_state VARCHAR(20) NOT NULL,
to_state VARCHAR(20) NOT NULL,
CONSTRAINT pk_allowed_transition PRIMARY KEY (from_state, to_state)
);
INSERT INTO allowed_transition (from_state, to_state) VALUES
('Candidate', 'Tenderer'),
('Tenderer', 'Winner');
-- Trigger: reject any status change whose (old, new) pair is not an allowed edge.
CREATE TRIGGER trg_org_status_transition
BEFORE UPDATE OF status ON organisation
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
BEGIN
SELECT CASE WHEN NOT EXISTS (
SELECT 1 FROM allowed_transition
WHERE from_state = OLD.status AND to_state = NEW.status
) THEN RAISE(ABORT, 'Illegal state transition') END;
END;
-- (2) HISTORY-BASED ORDERING -- the article's actual "tenderer before winner"
-- rule, which needs a history of states to assert a prior state occurred.
-- (organisation_role keeps one row per role episode with a start timestamp.)
CREATE TRIGGER trg_winner_requires_prior_tenderer
BEFORE INSERT ON organisation_role
FOR EACH ROW
WHEN (NEW.role = 'Winner')
BEGIN
SELECT CASE WHEN NOT EXISTS (
SELECT 1 FROM organisation_role t
WHERE t.org_id = NEW.org_id
AND t.role = 'Tenderer'
AND t.start_ts < NEW.start_ts
) THEN RAISE(ABORT, 'Winner requires a prior Tenderer episode') END;
END;
Notes:
- Form (1) is dialect-light and covers "legal next step" enforcement.
- Form (2) is the article's exact temporal ordering; on SQL:2011 system-versioned
tables the history table is implicit and the EXISTS queries the period view.
- Syntax above is SQLite-flavoured for readability; the generator would emit the
per-dialect equivalent (SQL Server, Oracle, PostgreSQL, etc.).
|
