Practical Declarative Data Integrity in Scientific Systems: Enforcing Cross-Table Business Rules in ELN and Inventory Workflows with Oracle 23ai Assertions

The Reality Behind Digital Labs

Over the past decade, life sciences organizations have accelerated their digital transformation initiatives. Electronic Lab Notebooks (ELNs) have moved from “innovation tools” to mission-critical platforms that hold experimental evidence, sample traceability, regulatory documentation, and intellectual property.

Industry research indicates that more than 70–80% of pharmaceutical R&D laboratories have adopted ELNs as part of modernization efforts, and the global ELN market continues to grow steadily as organizations phase out fragmented systems and paper-based workflows.

But adoption alone does not guarantee integrity.

When companies migrate from one ELN to another — or integrate ELN with LIMS, inventory systems, ERP, instrument data pipelines, or AI analytics platforms — the real challenge begins.

• Historical data must remain accurate.
• Scientific relationships must remain intact.
• Regulatory traceability must not break.
• Business rules must continue to hold true.

And here is where many digital transformation projects quietly struggle.

The Hidden Risk in ELN Migration

During ELN migrations or integrations:

• Bulk data loads bypass UI validations.
• Legacy scripts insert data directly into tables.
• Cross-functional teams perform SQL fixes.
• Old validation logic is not updated when business rules evolve.

Dynamic business models change faster than legacy scripts.

What was once “30 grams max per order” becomes 50 grams. What required QA approval always now requires QA approval only at specific workflow stages. What was once a static rule becomes conditional logic tied to regulatory flags.

But migration scripts written two years ago do not know that.

Triggers written five years ago do not know that.

Application validations embedded in older APIs do not know that.

The database silently accepts inconsistent states — and suddenly:

• Inventory rules are violated.
• Expired materials are requested.
• Restricted samples bypass approval workflows.
• Regulatory defensibility weakens.

To operationalize FAIR data principles (Findable, Accessible, Interoperable, Reusable), scientific systems must enforce integrity at the data source — ensuring consistency across migrations, integrations, and evolving business logic.

A Practical Inventory Example Inside an ELN Ecosystem

Let us model a simplified inventory ordering workflow within an ELN.

Business Rules:

• Requested samples must exist in the system.
• Quantity must be greater than zero.
• Total weight per order must not exceed 30 grams.
• Total liquid volume must not exceed 200 ml.
• Expired samples cannot be requested.
• Restricted samples require QA approval.

Schema Definition

CREATE TABLE sample (
    sample_id        NUMBER PRIMARY KEY,
    sample_type      VARCHAR2(20),
    unit_type        VARCHAR2(10), -- 'GRAM' or 'ML'
    available_qty    NUMBER,
    expiry_date      DATE,
    restricted_flag  CHAR(1)
);

CREATE TABLE orders (
    order_id     NUMBER PRIMARY KEY,
    status       VARCHAR2(20),
    qa_approved  CHAR(1)
);

CREATE TABLE order_items (
    order_id     NUMBER REFERENCES orders(order_id),
    sample_id    NUMBER REFERENCES sample(sample_id),
    quantity     NUMBER
);

Application-Level Validation (The Illusion of Safety)

The UI prevents negative quantities. The UI checks expiry dates. The UI enforces weight limits.

But the UI is not the only path to the database.

Basic Table Constraint

ALTER TABLE order_items
ADD CONSTRAINT chk_quantity_positive
CHECK (quantity > 0);

This is helpful — but insufficient.

It cannot:

• Sum quantities across rows.
• Check related tables.
• Enforce conditional approval logic.

The Traditional Trigger-Based Solution

1. Prevent Expired Samples

CREATE OR REPLACE TRIGGER trg_check_expiry
BEFORE INSERT OR UPDATE ON order_items
FOR EACH ROW
DECLARE
    v_expiry DATE;
BEGIN
    SELECT expiry_date INTO v_expiry
    FROM sample
    WHERE sample_id = :NEW.sample_id;

    IF v_expiry < SYSDATE THEN
        RAISE_APPLICATION_ERROR(-20001, 'Sample expired');
    END IF;
END;
/

2. Enforce Weight Limit

CREATE OR REPLACE TRIGGER trg_check_weight
BEFORE INSERT OR UPDATE ON order_items
FOR EACH ROW
DECLARE
    total_weight NUMBER;
BEGIN
    SELECT NVL(SUM(oi.quantity),0)
    INTO total_weight
    FROM order_items oi
    JOIN sample s ON s.sample_id = oi.sample_id
    WHERE oi.order_id = :NEW.order_id
      AND s.unit_type = 'GRAM';

    IF total_weight + :NEW.quantity > 30 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Weight limit exceeded');
    END IF;
END;
/

3. Enforce Volume Limit

CREATE OR REPLACE TRIGGER trg_check_volume
BEFORE INSERT OR UPDATE ON order_items
FOR EACH ROW
DECLARE
    total_volume NUMBER;
BEGIN
    SELECT NVL(SUM(oi.quantity),0)
    INTO total_volume
    FROM order_items oi
    JOIN sample s ON s.sample_id = oi.sample_id
    WHERE oi.order_id = :NEW.order_id
      AND s.unit_type = 'ML';

    IF total_volume + :NEW.quantity > 200 THEN
        RAISE_APPLICATION_ERROR(-20003, 'Volume limit exceeded');
    END IF;
END;
/

4. Restricted Sample Requires QA Approval

CREATE OR REPLACE TRIGGER trg_check_restricted
BEFORE INSERT OR UPDATE ON order_items
FOR EACH ROW
DECLARE
    v_restricted CHAR(1);
    v_qa CHAR(1);
BEGIN
    SELECT restricted_flag INTO v_restricted
    FROM sample WHERE sample_id = :NEW.sample_id;

    SELECT qa_approved INTO v_qa
    FROM orders WHERE order_id = :NEW.order_id;

    IF v_restricted = 'Y' AND v_qa <> 'Y' THEN
        RAISE_APPLICATION_ERROR(-20004, 'QA approval required');
    END IF;
END;
/

Now imagine the business changes:

• Weight limit becomes 50 grams.
• QA approval required only when order status = 'SUBMITTED'.
• Combined weight and volume must not exceed global threshold.

Multiple triggers must be rewritten. Testing becomes complicated. Legacy scripts may not reflect the new logic.

Integrity becomes fragmented.

Demonstration Data

INSERT INTO sample VALUES (1, 'CHEMICAL', 'GRAM', 100, SYSDATE + 10, 'N');
INSERT INTO sample VALUES (2, 'LIQUID', 'ML', 500, SYSDATE + 10, 'Y');

INSERT INTO orders VALUES (101, 'CREATED', 'N');

INSERT INTO order_items VALUES (101, 1, 25);
INSERT INTO order_items VALUES (101, 1, 10); -- Should fail (weight > 30)

Enter Oracle 23ai Assertions

Oracle 23ai introduces SQL Assertions — declarative, schema-level constraints that validate cross-row and cross-table logic.

Instead of procedural sprawl, we define integrity once.

CREATE ASSERTION order_integrity_assertion
CHECK (
  NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE
      (
        SELECT SUM(oi.quantity)
        FROM order_items oi
        JOIN sample s ON s.sample_id = oi.sample_id
        WHERE oi.order_id = o.order_id
          AND s.unit_type = 'GRAM'
      ) > 30
      OR
      (
        SELECT SUM(oi.quantity)
        FROM order_items oi
        JOIN sample s ON s.sample_id = oi.sample_id
        WHERE oi.order_id = o.order_id
          AND s.unit_type = 'ML'
      ) > 200
      OR EXISTS (
        SELECT 1
        FROM order_items oi
        JOIN sample s ON s.sample_id = oi.sample_id
        WHERE oi.order_id = o.order_id
          AND s.expiry_date < SYSDATE
      )
      OR EXISTS (
        SELECT 1
        FROM order_items oi
        JOIN sample s ON s.sample_id = oi.sample_id
        WHERE oi.order_id = o.order_id
          AND s.restricted_flag = 'Y'
          AND o.qa_approved <> 'Y'
      )
  )
);

What Changes Architecturally?

• One centralized rule.
• No trigger ordering concerns.
• No duplication across systems.
• No reliance on UI discipline.
• Safe during migrations and integrations.

When business logic evolves, the assertion evolves.

The database becomes the guardian of integrity.

Closing Reflection

In scientific systems, data is not just information — it is evidence.

Evidence must survive migrations. Evidence must survive integrations. Evidence must survive business evolution.

Assertions in Oracle 23ai represent more than a new feature. They represent a shift from procedural enforcement to declarative integrity — a foundational step toward resilient, FAIR-aligned, and compliance-ready data architectures.