PL/SQL anti-pattern

PL/SQL trigger side effects

Row-level triggers that change values, write to other tables and enforce rules on every DML statement, so business behavior fires no matter which application, script or batch job touched the row.

Request a codebase scan Explore demo

FAMILY: PL/SQL
CUTOVER RISK: Critical; Changes runtime behavior in ways a line-by-line reading misses. Resolve before any cutover plan.
MODERN MAPPING: Explicit domain operation with events via an outbox
GENERATED ARTIFACT: asyncapi.yaml

Before and after

legacy source → modern mapping

Legacy: exhibits the pattern

trg_loan_bu.sqlSQL

1CREATE OR REPLACE TRIGGER trg_loan_bu
2BEFORE UPDATE OF status, balance ON loans
3FOR EACH ROW
4BEGIN
5  IF :NEW.status = 'DEFAULT'
6     AND :OLD.status <> 'DEFAULT' THEN
7    :NEW.default_date := SYSDATE;
8    :NEW.rate := :OLD.rate + 0.0200;   -- penalty rate
9    INSERT INTO collections_queue (loan_id, queued_at)
10    VALUES (:NEW.loan_id, SYSDATE);
11  END IF;
12  IF :NEW.balance < 0 THEN
13    RAISE_APPLICATION_ERROR(-20010, 'Negative balance');
14  END IF;
15END;

Modern: Explicit domain operation with events via an outbox

Loan.javaJava

1public void markDefaulted(Clock clock) {
2    if (status == LoanStatus.DEFAULT) {
3        return;                       // :OLD.status check
4    }
5    status = LoanStatus.DEFAULT;
6    defaultDate = LocalDate.now(clock);
7    rate = rate.add(PENALTY_RATE);    // +0.0200, R-LN-07
8    events.add(new LoanDefaulted(id, defaultDate));
9}
10 
11public void applyPayment(BigDecimal amount) {
12    BigDecimal next = balance.subtract(amount);
13    if (next.signum() < 0) {
14        throw new LoanRuleException("-20010");
15    }
16    balance = next;
17}

Why it is dangerous in modernization

A BEFORE UPDATE FOR EACH ROW trigger can rewrite :NEW values, insert into queue tables and raise application errors, and none of it is visible to the application that issued the UPDATE. Over decades, triggers become the place where teams put rules they wanted enforced everywhere: penalty rates on default, audit rows, derived balances, queue entries for downstream jobs. When the data moves to a new database or the logic moves into services, triggers are easy to forget because no application code calls them. The new service updates a loan's status and nothing else happens: no penalty rate, no collections queue entry, no default date. Triggers also have their own edge cases: they do not fire on direct path loads, they can hit ORA-04091 (mutating table) when they query their own table, and comparisons with NULL quietly skip their logic.

How PYD ENTERPRISES detects it

PYD ENTERPRISES parses trigger DDL with its timing, events, column lists (UPDATE OF), WHEN clause and body, and links each trigger to its table in the column-level lineage graph. Every DML statement found in applications, packages and scripts is then matched to the triggers it fires, so each write path shows its hidden side effects. Assignments to :NEW are extracted as derivation rules, inserts into other tables as events, and RAISE_APPLICATION_ERROR calls as constraints with their error numbers. Compound triggers and package variables used to work around mutating-table errors are recognised and reported as cross-row state.

Maps to: Explicit domain operation with events via an outbox

Each trigger's derivations become part of the domain operation that changes the state, such as markDefaulted(), so the rule runs because the business operation runs. Inserts into queue tables become domain events published through a transactional outbox, described in AsyncAPI. Constraints become validations in the service and, where they protect data integrity, CHECK constraints in the new schema.

Generated specification fragment

asyncapi.yamlYAML

1asyncapi: '2.6.0'
2info:
3  title: Loan events
4  version: '1.0.0'
5channels:
6  loan.defaulted:
7    subscribe:
8      operationId: onLoanDefaulted
9      description: Replaces INSERT INTO collections_queue
10        in trg_loan_bu (trg_loan_bu.sql:9-10)
11      message:
12        payload:
13          type: object
14          required: [loanId, defaultDate]
15          properties:
16            loanId: { type: string }
17            defaultDate: { type: string, format: date }

Every generated element links back to the source file and line range shown in the legacy pane. Review and sign off before building against it.

The NULL case nobody tested

:OLD.status <> 'DEFAULT' is not true when :OLD.status is NULL; in SQL the comparison yields unknown and the IF branch is skipped. A loan inserted without a status and later moved straight to DEFAULT gets no penalty rate and no collections entry. The engine flags comparisons on nullable columns inside trigger conditions, using the column's nullability from the table DDL, and generates a fixture with the NULL case so the team can decide whether the new markDefaulted() should reproduce or correct it. In the Java shown above, the NULL case is corrected, deliberately, and that decision is recorded in the specification.

Writes that bypass triggers

SQL*Loader direct path loads do not fire insert triggers.

Data Pump imports and partition exchanges move rows without row triggers.

Triggers can be disabled during maintenance windows and re-enabled afterwards.

If legacy batch loads use any of these, the trigger's rule never applied to those rows, and the historical data is inconsistent with the rule. PYD ENTERPRISES lists loaders and scripts found in the ingest that write to triggered tables by such paths, which helps the data migration team reconcile history before cutover.

Ordering with other triggers

A table can have several triggers for the same timing and event. Without a FOLLOWS clause, Oracle does not guarantee their firing order. When two BEFORE ROW triggers both assign :NEW.rate, the result depends on an order the database does not promise. The engine reports overlapping assignments across triggers on the same table as a finding of its own, because no deterministic specification can be extracted until someone decides which one wins.

Related patterns

Usually found in the same codebase.

Back to the full catalog

Find every PL/SQL trigger side effects in your codebase.

Run PYD ENTERPRISES in your private VPC and get each occurrence with file, line range, modern mapping and equivalence fixtures, ready for your architects to review.

Start 14-day trial Book technical walkthrough