Questions: Database Triggers and Automated Event Handling

Question 1

A database designer wants to ensure that whenever a new order is inserted, the order's total price is automatically rounded to two decimal places. Which trigger type is most appropriate?

• A. AFTER INSERT FOR EACH ROW — it runs after the data is stored, so you can correct it
• B. BEFORE INSERT FOR EACH ROW — it intercepts the data before writing and can modify NEW.total_price
• C. AFTER INSERT FOR EACH STATEMENT — it handles all rows at once for efficiency
• D. Either BEFORE or AFTER works identically for data modification

Answer: (B)

A BEFORE trigger fires before the row is written to disk and has access to NEW (the incoming data). This is the correct tool for transformation: you can directly modify NEW.total_price before it's stored. An AFTER trigger fires after the change is already committed to the table — at that point, modifying the data requires a separate UPDATE statement, which is slower and creates an additional trigger fire. BEFORE triggers are the idiomatic choice for validation and transformation.

Question 2

A bulk UPDATE statement modifies 10,000 rows. How many times does a FOR EACH ROW trigger on that table execute, compared to a statement-level trigger?

• A. Both execute exactly once, since it is one statement
• B. The FOR EACH ROW trigger executes 10,000 times; the statement-level trigger executes once
• C. The FOR EACH ROW trigger executes once; the statement-level trigger executes 10,000 times
• D. Both execute 10,000 times for a bulk UPDATE

Answer: (B)

FOR EACH ROW triggers bind OLD and NEW to each affected row and fire for every row the statement touches — 10,000 times here. Statement-level triggers fire once per SQL statement regardless of rows affected. This difference has major performance implications: a FOR EACH ROW trigger that does expensive work (like writing to an audit table) will execute 10,000 times. For bulk operations where you only need to log that a batch occurred, a statement-level trigger is far more efficient.

Question 3

An AFTER trigger can cause the data change that fired it to be rolled back.

• T. True
• F. False

Answer: True

Although BEFORE triggers are the natural tool for cancellation (they can raise an error before the write occurs), AFTER triggers run inside the same transaction as the triggering statement. Raising an exception inside an AFTER trigger will roll back the entire transaction, including the data change. This is less common than using BEFORE for validation, but it is possible. Both trigger types run within the same transaction boundary.

Question 4

Because triggers fire automatically regardless of which application modifies the data, they are the best place to enforce most business logic in a database application.

• T. True
• F. False

Answer: False

Triggers are appropriate for cross-cutting concerns that must be enforced regardless of which application touches the data — audit logging, referential integrity, derived column maintenance. But placing general business logic in triggers creates serious maintainability problems: triggers fire invisibly, making debugging difficult. Trigger chains (one trigger firing another) can produce complex, opaque execution paths and even infinite loops. The standard guidance is to keep business logic in the application layer where it's visible and testable, using triggers only for concerns genuinely tied to the data layer.

Question 5

Explain the key difference between what a BEFORE trigger and an AFTER trigger can accomplish, and give an example of an appropriate use case for each.

Model answer: A BEFORE trigger fires before the row is written to the table and can access and modify the NEW row — making it ideal for validation (reject the insert if a field is invalid) and transformation (normalize a phone number format, auto-set a created_at timestamp). An AFTER trigger fires after the change is successfully committed to the table — making it ideal for side effects like writing to an audit log, updating a summary counter in another table, or sending notifications. You cannot use an AFTER trigger to silently modify the data that was just written without issuing a separate UPDATE.

The timing determines capability: BEFORE = intercept and shape; AFTER = react and propagate. Mixing these up leads to either ineffective code (trying to validate after data is already written) or unnecessary complexity (issuing a corrective UPDATE in an AFTER trigger when a BEFORE trigger could have prevented the bad data in the first place).