Explain Database Triggers in EBS Customizations in Oracle Applications

16 / 60

PL/SQLintermediate

Database Triggers in EBS Customizations

Explain the appropriate and risky uses of database triggers within an Oracle EBS environment

A trigger on an EBS base table is almost always the wrong answer, and knowing why is the point of this topic.

Adding a custom trigger to a seeded EBS table is like splicing your own wiring into a building's fire alarm system — it might work today, but the next time the fire department (Oracle patch) services the panel, your unofficial wiring could get ripped out or cause a false alarm.

Key Concepts

The standard trap: "would you add a trigger on AP_INVOICES_ALL for custom validation?"No. Three reasons, and they are all concrete.

AP_INVOICES_ALL

Oracle's own triggers already fire there. Your trigger's order relative to them is not something you control, so your validation may run before or after logic it depends on.

Mutating table errors. A row-level trigger that queries the table it is firing on raises ORA-04091. Validation that needs to look at other rows of the same table — "no more than three open invoices per supplier" — hits this immediately.

ORA-04091

Patches. Oracle changes seeded tables and their triggers. A custom trigger is not in Oracle's inventory, so nothing warns you when it breaks, and revalidating every custom trigger is added to every upgrade.

What to use instead, in Oracle's own order of preference:

    API user hooks     where the API provides an extension point
    Business Events    react to something that happened, in workflow
    Forms Personalization  UI-level validation
    CUSTOM.pll         Forms-level logic across screens

Each of these is a place Oracle intends you to put code, so it survives patching and has a defined firing point.

Triggers on your own custom tables are fine, following ordinary practice: keep the logic short, avoid querying the triggering table, and do not chain triggers into each other.

Compound triggers solve the mutating table problem where a trigger genuinely is right. They combine the timing points in one object with shared state:

    CREATE OR REPLACE TRIGGER trg_example
    FOR INSERT OR UPDATE ON xx_custom_table
    COMPOUND TRIGGER
      TYPE t_ids IS TABLE OF NUMBER;
      g_ids t_ids := t_ids();

      AFTER EACH ROW IS BEGIN
        g_ids.EXTEND; g_ids(g_ids.COUNT) := :NEW.id;     -- collect
      END AFTER EACH ROW;

      AFTER STATEMENT IS BEGIN
        -- now safe to query the table
      END AFTER STATEMENT;
    END;

Collect the affected ids row by row, then do the work once the statement has finished and the table is no longer mutating.

The governance point. EBS customisation has an order of preference — configuration, then supported extension points, then custom objects, and never modifying seeded code. A trigger on a seeded table sits outside that hierarchy, which is why the answer is no even when it would work.

What the interviewer is probing.1. "When is a trigger the wrong answer in EBS?" Probing: judgement. Stalls: "Never." *Moves up:* almost always on seeded tables — it is unsupported, invisible to the next person, and a patch can drop or recreate the table.

2. "What is the supported alternative?" Probing: the knowledge. Stalls: "Nothing." *Moves up:* business events and subscriptions, CUSTOM.pll, form personalisation, or a public API — all survive patching.

3. "What is a mutating table error?" Probing: the classic. Stalls: "A lock." Moves up: a row-level trigger querying the table being modified, which the compound trigger or a statement-level approach resolves.

4. "If a trigger is unavoidable, what do you do?" Probing: the pragmatism. Stalls: "Just write it." Moves up: keep it tiny, put the logic in a package, document it in the custom object register, and retest it after every patch.