A cross-row rule in a trigger is a hope, not a guarantee: use CREATE ASSERTION

Rules like "no double bookings" or "every order has at least one line" span many rows. For years we coded them in triggers or app code and hoped two sessions never raced. Starting in release 23.26.1, Oracle AI Database 26ai adds CREATE ASSERTION: a cross-row, cross-table rule written in SQL and enforced by the database.

What to know

• Write the rule as a query: NOT EXISTS, or the new ALL ... SATISFY form, which needs fewer negations. A change that breaks it fails with ORA-08601.
• DEFERRABLE INITIALLY DEFERRED moves the check to commit, so a parent and its children can go in one transaction. The default is a statement-level check.
• The database resolves concurrency for you: no hand-rolled locks. Assertions work in READ COMMITTED, not SERIALIZABLE.
• On big tables, ENABLE NOVALIDATE skips existing rows so creation is instant; validate later with ALTER ASSERTION ... VALIDATE. Index the columns the rule joins and filters on.

Why now: APEX pages, APIs, batch jobs and now AI agents all write to the same tables. Declare the rule once and the database enforces it for every one of them. Try it on a test schema in the 26ai Free container from the linked guide.

Sources: Cross-table constraints with assertions, Assertion performance, CREATE ASSERTION reference.