Assertions Use Cases in Oracle
Assertions are boolean expressions that have the semantics of a constraint. The database must ensure the truth of these boolean expressions while transactions change data and commit these changes.
Prerequisites
Assertions are schema-level objects. If the boolean expression references tables of other schemas, you must have the required object-privileges to access these tables.
When a foreign key constraint references a parent table in another schema, you are required to have the REFERENCES object privilege on the parent table.
For assertions to reference tables in another schema you require the ASSERTION REFERENCES object privilege on those tables. Also note that you must prefix these tables from other schemas with their schema-name (SYNONYMs are unsupported in assertions).
To create a assertion in your own schema, you must have the CREATE ASSERTION system privilege. With the CREATE ASSERTION privilege you can alter and drop assertions in the same schema using ALTER ASSERTION and DROP ASSERTION.
To create assertions in any schema or a specified schema, you must have the CREATE ANY ASSERTION [ON SCHEMA...] system privilege.
The following SQL statement creates an assertion, named NO_TRAINERS_IN_BOSTON, to demand that there are no trainers employed in Boston-based departments. The top-level NOT EXISTS represents a Boolean expression which demands that the result set of the join between the EMP and DEPT tables is to remain empty.
CREATE ASSERTION no_trainers_in_boston CHECK
(NOT EXISTS
(SELECT 'trainer in Boston'
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.job = 'TRAINER'
AND d.loc = 'BOSTON'));Example for Assertions Use Cases :
The Problem: The “Impossible Promotion” in an Event Sourcing Pipeline
The Scenario:
You are building an analytics system for a retail bank. You have a table that tracks the state of customer accounts over time based on an event stream (deposits, withdrawals, interest applied).
The table tracks the account_id, a timestamp (status_timestamp), the current balance, and the current account tier (Standard, Gold, Platinum).
The Business Rule (The Invariant):
A fundamental rule in the banking domain is that an account cannot skip tiers when being promoted. An account must go from Standard -> Gold -> Platinum. It cannot jump directly from Standard to Platinum in a single state update.
The Solution for Oracle Database (Pre-23.26.1)
Since Oracle didn’t support true assertions until version 23.26.1, we need to implement the “no skipping tiers” rule using a combination of features that have been available for decades .
Option 1: The Compound Trigger Approach (Most Elegant for Oracle 11g+)
The Challenge with Simple Triggers:
If we try to write a simple row-level trigger that queries the account_history table, we’ll hit the infamous ORA-04091: mutating table error. This happens because the table is in flux during the DML operation .
The Solution: Compound Triggers (Oracle 11g+)
Compound triggers allow us to collect changes at the row level and validate them after the table stabilizes .
-- Create the table (standard Oracle syntax)
CREATE TABLE account_history (
account_id NUMBER(10),
status_timestamp TIMESTAMP(3),
balance NUMBER(15,2),
tier VARCHAR2(10) CHECK (tier IN ('Standard', 'Gold', 'Platinum')),
CONSTRAINT pk_account_history PRIMARY KEY (account_id, status_timestamp)
)
ORGANIZATION INDEX; -- Good for time-series data
-- Create a sequence for tie-breaking if needed
CREATE SEQUENCE seq_account_history;
-- The compound trigger that enforces the "no tier skipping" rule
CREATE OR REPLACE TRIGGER trg_no_illegal_promotion
FOR INSERT OR UPDATE ON account_history
COMPOUND TRIGGER
-- Type to hold changed rows in memory
TYPE t_change_rec IS RECORD (
account_id account_history.account_id%TYPE,
new_tier account_history.tier%TYPE,
new_timestamp account_history.status_timestamp%TYPE
);
TYPE t_change_tab IS TABLE OF t_change_rec INDEX BY PLS_INTEGER;
g_changes t_change_tab;
g_idx PLS_INTEGER := 0;
-- BEFORE EACH ROW: Collect all changes
BEFORE EACH ROW IS
BEGIN
g_idx := g_idx + 1;
g_changes(g_idx).account_id := :NEW.account_id;
g_changes(g_idx).new_tier := :NEW.tier;
g_changes(g_idx).new_timestamp := :NEW.status_timestamp;
END BEFORE EACH ROW;
-- AFTER STATEMENT: Validate all collected changes
AFTER STATEMENT IS
v_previous_tier VARCHAR2(10);
v_tier_level NUMBER;
v_prev_level NUMBER;
BEGIN
FOR i IN 1 .. g_changes.COUNT LOOP
-- Find the most recent tier BEFORE this change for the same account
BEGIN
SELECT tier INTO v_previous_tier
FROM account_history
WHERE account_id = g_changes(i).account_id
AND status_timestamp < g_changes(i).new_timestamp
ORDER BY status_timestamp DESC
FETCH FIRST 1 ROW ONLY;
-- Map tiers to numeric values for comparison
v_tier_level := CASE g_changes(i).new_tier
WHEN 'Standard' THEN 1
WHEN 'Gold' THEN 2
WHEN 'Platinum' THEN 3
END;
v_prev_level := CASE v_previous_tier
WHEN 'Standard' THEN 1
WHEN 'Gold' THEN 2
WHEN 'Platinum' THEN 3
END;
-- Check for illegal skip
IF v_tier_level - v_prev_level > 1 THEN
RAISE_APPLICATION_ERROR(-20001,
'Illegal tier promotion: Cannot skip from ' ||
v_previous_tier || ' to ' || g_changes(i).new_tier);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- First record for this account, no previous tier to check
NULL;
END;
END LOOP;
-- Clear the collection
g_changes.DELETE;
END AFTER STATEMENT;
END trg_no_illegal_promotion;
/
How It Works:
- The BEFORE EACH ROW section captures all changed rows in a PL/SQL collection, avoiding mutating table errors
- The AFTER STATEMENT section runs when the table is stable and validates every change
- It checks each account’s history to ensure tiers increase by only one level at a time
Option 2: The Materialized View with Constraint Approach
This approach leverages Oracle’s materialized view capabilities to enforce cross-row constraints .
-- Step 1: Create a materialized view log to support fast refresh
CREATE MATERIALIZED VIEW LOG ON account_history
WITH ROWID, (account_id, tier, status_timestamp)
INCLUDING NEW VALUES;
-- Step 2: Create a materialized view that detects violations
CREATE MATERIALIZED VIEW mv_tier_violation
REFRESH FAST ON COMMIT
AS
-- This view identifies any account that skipped a tier
SELECT DISTINCT curr.account_id
FROM account_history curr, account_history prev
WHERE curr.account_id = prev.account_id
AND curr.status_timestamp > prev.status_timestamp
AND NOT EXISTS (
SELECT 1 FROM account_history mid
WHERE mid.account_id = curr.account_id
AND mid.status_timestamp > prev.status_timestamp
AND mid.status_timestamp < curr.status_timestamp
)
AND (
(prev.tier = 'Standard' AND curr.tier = 'Platinum') OR
(prev.tier = 'Standard' AND curr.tier = 'Gold' AND
EXISTS (SELECT 1 FROM account_history
WHERE account_id = curr.account_id
AND tier = 'Platinum'
AND status_timestamp > curr.status_timestamp)) OR
(prev.tier = 'Gold' AND curr.tier = 'Standard')
);
-- Step 3: Add a constraint that the MV must be empty
ALTER TABLE mv_tier_violation
ADD CONSTRAINT ck_no_tier_skip CHECK (1=0) DISABLE;
-- Any transaction that causes a violation will leave a row in the MV
-- and fail on COMMIT with ORA-12048
Option 3: The Oracle 23.26.1+ Native Assertion
If you’re on the latest Oracle version, you can use the native CREATE ASSERTION :
CREATE ASSERTION no_illegal_promotion
CHECK (
NOT EXISTS (
SELECT 1
FROM account_history curr
WHERE EXISTS (
SELECT 1
FROM account_history prev
WHERE prev.account_id = curr.account_id
AND prev.status_timestamp < curr.status_timestamp
AND NOT EXISTS (
SELECT 1
FROM account_history mid
WHERE mid.account_id = curr.account_id
AND mid.status_timestamp > prev.status_timestamp
AND mid.status_timestamp < curr.status_timestamp
)
AND (
(prev.tier = 'Standard' AND curr.tier = 'Platinum') OR
(prev.tier = 'Gold' AND curr.tier = 'Standard')
)
)
)
);
Why This Matters for Oracle
The key insight is that complex temporal business rules can be enforced in any Oracle version, but the approach differs dramatically based on your version :
Oracle Version Recommended Approach Complexity Performance
23.26.1+ Native CREATE ASSERTION Low Best
11g – 19c Compound Triggers Medium Good
Any Version Materialized View + Constraint High Moderate
Legacy (pre-11g) Multiple Triggers + Package Vars Very High Poor
The Real-World Impact
Banks running on Oracle 11g (still common in 2026!) can implement this pattern today. When a trading system bug tries to promote a customer from Silver to Platinum in one step, the compound trigger catches it before corrupted data reaches the reporting warehouse. This prevents incorrect executive decisions based on impossible customer journeys .
The beauty of the compound trigger approach is that it maintains read consistency—the validation sees the table as it was before your transaction, combined with your current changes, ensuring no phantom reads or missed violations .
