This page needs a newer browser to run its tools. You can still read everything here.

PL/SQL & T-SQL (Oracle and SQL Server) Module 12

Triggers in Oracle interview questions

Interview questions on triggers in Oracle: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

8 questions: 3 warm-ups and 5 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

How does a row trigger differ from a statement trigger?

Answered in the lesson Oracle DML triggers: row, statement, INSTEAD OF
Fresher to mid

When does :OLD or :NEW hold nothing?

Answered in the lesson Oracle DML triggers: row, statement, INSTEAD OF
Mid

What is a mutating table, and which triggers does the rule apply to?

Answered in the lesson Compound triggers and the mutating-table error

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Fresher to senior

A BEFORE UPDATE trigger on stock recalculates a discount for every row it fires on, and a nightly job that only corrects the on_hand count now changes prices too. The trigger was meant to run only when unit_price changes. Explain what went wrong and how you would scope the trigger.
What it tests

Whether the candidate knows a row trigger fires for every affected row, and can scope it with a column list (UPDATE OF), an UPDATING('col') test and a WHEN clause.

First step

The trigger has no column list and no UPDATING test, so it runs its discount logic even when only on_hand changes. Scope it to the price with BEFORE UPDATE OF unit_price and guard the body with UPDATING('UNIT_PRICE').
Follow-ups
- What is the difference between BEFORE UPDATE OF unit_price and testing UPDATING('UNIT_PRICE') in the body?
- How would a WHEN clause reduce the rows the body runs for?
- Would a statement trigger have avoided this, and what would it lose?
From the lesson Oracle DML triggers: row, statement, INSTEAD OF
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A teammate sees three BEFORE INSERT triggers on orders: one fills a surrogate key from a sequence, one defaults the order date, and one rejects a non-positive quantity. Which of the three would you replace, and with what?
What it tests

Whether the candidate prefers declarative features over triggers: identity columns, DEFAULT ON NULL and CHECK constraints, and can say what each guarantees that a trigger does not.

First step

All three can be declarative. The key becomes GENERATED ALWAYS AS IDENTITY, the date a DEFAULT ON NULL, and the quantity rule a CHECK (qty > 0) constraint, each enforced by the engine itself, on every path into the table.
Follow-ups
- Why is an identity column safer than a sequence read in a trigger?
- When is DEFAULT ON NULL better than a plain DEFAULT?
- Which rule here could a constraint not express, and would justify keeping a trigger?
From the lesson Oracle DML triggers: row, statement, INSTEAD OF
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A row trigger on accounts keeps each customer's total at or below a cap with SELECT SUM(balance) for the row's customer. Its first test, a 10 % interest UPDATE, fails with ORA-04091 although no customer goes over. Explain the error and rewrite the check.
What it tests

Whether the candidate knows the mutating-table restriction on row triggers, can explain why a halfway read is unreliable, and can move the check into a compound trigger's statement section.

First step

The row trigger queries the table its own UPDATE is changing, which a row-level trigger may not do. Keep the per-row part to recording which customers changed, and do the SUM after the statement, in the AFTER STATEMENT section of a compound trigger.
Follow-ups
- Why does Oracle refuse the read instead of returning the current totals?
- Which customers does the statement section check, and how do you avoid checking one twice?
- What does a statement that changes no rows do to the trigger?
- When is a plain AFTER statement trigger enough?
From the lesson Compound triggers and the mutating-table error
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to staff

To get past ORA-04091, a teammate added PRAGMA AUTONOMOUS_TRANSACTION to the row trigger that enforces a balance cap. The error is gone and the existing tests pass. What would you check, and what would you ship instead?
What it tests

Whether the candidate knows an autonomous transaction reads only committed data, can name a statement that breaks the rule and still passes, and knows the other costs of autonomous triggers.

First step

Write a test that must fail: a deposit that takes one customer above the cap. With the autonomous trigger it passes, because the trigger's query reads the committed balances, not the row the statement has just changed.
Follow-ups
- Which statements does the autonomous version get right, and why only by luck?
- What other risk does an autonomous transaction inside a trigger bring?
- Why can a compound trigger not be made autonomous?
From the lesson Compound triggers and the mutating-table error
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

You must add an AFTER LOGON ON DATABASE trigger that records every new session. How do you make sure a bug in it cannot lock users out, and how do you test it before anyone depends on it?
What it tests

Whether the candidate knows an unhandled error in a logon trigger refuses the logon for users without ADMINISTER DATABASE TRIGGER, designs the handler for that, and tests with a privileged session open.

First step

Start from the failure: an unhandled error rolls back the triggering statement, and for a logon trigger that statement is the logon. Keep the body to one insert, decide what an error should mean, and reproduce a failure in a lab with a privileged session open.
Follow-ups
- Who can still connect when such a trigger fails, and why does that matter for the fix?
- When should a logon trigger refuse a session on purpose?
- How would you notice that audit rows are missing?
From the lesson Compound triggers and the mutating-table error
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes

All PL/SQL & T-SQL (Oracle and SQL Server) interview questions · The PL/SQL & T-SQL (Oracle and SQL Server) track

Warm-up questions

How does a row trigger differ from a statement trigger?

When does :OLD or :NEW hold nothing?

What is a mutating table, and which triggers does the rule apply to?

Questions with model answers

A BEFORE UPDATE trigger on stock recalculates a discount for every row it fires on, and a nightly job that only corrects the on_hand count now changes prices too. The trigger was meant to run only when unit_price changes. Explain what went wrong and how you would scope the trigger.

A teammate sees three BEFORE INSERT triggers on orders: one fills a surrogate key from a sequence, one defaults the order date, and one rejects a non-positive quantity. Which of the three would you replace, and with what?

A row trigger on accounts keeps each customer's total at or below a cap with SELECT SUM(balance) for the row's customer. Its first test, a 10 % interest UPDATE, fails with ORA-04091 although no customer goes over. Explain the error and rewrite the check.

To get past ORA-04091, a teammate added PRAGMA AUTONOMOUS_TRANSACTION to the row trigger that enforces a balance cap. The error is gone and the existing tests pass. What would you check, and what would you ship instead?

You must add an AFTER LOGON ON DATABASE trigger that records every new session. How do you make sure a bug in it cannot lock users out, and how do you test it before anyone depends on it?