Question

what happens when an oracle trigger fires

Vault Verified
Curated Intelligence
Definitive Source
Answer

It intercepts your SQL statement mid-stride.

Think of it as an invisible detour inside your active transaction. The moment the event happens, Oracle pauses the calling command, runs your PL/SQL block right there and only lets the transaction proceed if the trigger finishes cleanly. That shared transaction scope is the whole key. It shares the exact same locks as the original SQL. If the trigger code throws an unhandled exception or deliberately raises an error, the whole operation aborts and rolls back.

Most people hook them up to data changes like an INSERT, UPDATE or DELETE on a table. But you can also tie them to system events, like an error being raised, a user logging in or the entire database shutting down.

You control the timing. A BEFORE trigger runs before Oracle touches the table, which is where you validate inputs or set default timestamps; throw an application error here and no bad data ever touches disk. An AFTER trigger runs once the change is staged, making it the natural place for audit trails where you've got to log the final state. Then there're INSTEAD OF triggers, which are specifically for views (you use them to redirect an update into the real base tables underneath).

How often it runs depends on whether you've added FOR EACH ROW.

Without it, it's a statement-level trigger. It runs once per query, even if that query modifies ten thousand rows. With FOR EACH ROW, it fires for every single record touched. Row-level code gets access to the :OLD and :NEW pseudo-records. If you bump a salary from $50,000 to $60,000, :OLD.salary contains 50000 while :NEW.salary holds 60000.

Keep them light. Stuffing heavy calculations, outbound emails or remote web calls into a row trigger drags bulk updates to a crawl and holds locks open way longer than you'd ever want.

Related Questions