AFTER INSERT triggers in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of AFTER INSERT trigger updating another table

Consider two tables: orders and order_log. An AFTER INSERT trigger on orders inserts a log entry into order_log with the new order's ID and timestamp.

Given the following trigger and insert statement, what will be the content of order_log after the insert?

MySQL

CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT, product VARCHAR(50));
CREATE TABLE order_log (order_id INT, log_time DATETIME);
DELIMITER $$
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_log(order_id, log_time) VALUES (NEW.id, NOW());
END$$
DELIMITER ;

INSERT INTO orders (product) VALUES ('Book');

A []

B [{"order_id": 1, "log_time": "<current_timestamp>"}]

C [{"order_id": null, "log_time": "<current_timestamp>"}]

D Syntax error in trigger

💡 Hint

AFTER INSERT triggers run after the row is inserted, so NEW.id is available.

The AFTER INSERT trigger runs after the new row is inserted into orders. The NEW.id contains the auto-incremented ID (1 in this case). The trigger inserts a row into order_log with this ID and the current timestamp.

📝 Syntax

intermediate

Identify the syntax error in AFTER INSERT trigger

Which option contains a syntax error in this AFTER INSERT trigger definition?

MySQL

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  UPDATE departments SET count = count + 1 WHERE id = NEW.department_id;
END;

A Missing DELIMITER change before trigger creation

B Trigger body must be a single statement without BEGIN...END

C NEW keyword cannot be used in AFTER INSERT triggers

D FOR EACH ROW should be FOR EACH STATEMENT

💡 Hint

MySQL requires changing the delimiter when defining triggers with multiple statements.

In MySQL, when defining triggers with BEGIN...END blocks, you must change the delimiter temporarily to avoid syntax errors. Without changing the delimiter, the CREATE TRIGGER statement will fail.

❓ optimization

advanced

Optimizing AFTER INSERT trigger to avoid performance issues

An AFTER INSERT trigger on a large sales table updates a totals table by summing all sales for the inserted product. This causes slow inserts.

Which option optimizes the trigger to improve performance?

A Use BEFORE INSERT trigger to update totals

B Update totals by summing all sales for the product inside the trigger

C Remove the trigger and update totals manually once a day

D Increment totals by NEW.sale_amount instead of summing all sales

💡 Hint

Avoid recalculating sums over large tables inside triggers.

Incrementing totals by the inserted sale amount avoids scanning the entire sales table, improving performance significantly.

🔧 Debug

advanced

Why does this AFTER INSERT trigger cause a deadlock?

Given two tables accounts and transactions, an AFTER INSERT trigger on transactions updates the balance in accounts. Sometimes, inserting into transactions causes a deadlock.

What is the most likely cause?

MySQL

CREATE TRIGGER after_transaction_insert
AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
  UPDATE accounts SET balance = balance + NEW.amount WHERE id = NEW.account_id;
END;

A NEW keyword is invalid in AFTER INSERT triggers

B AFTER INSERT triggers cannot update other tables

C Trigger updates accounts causing lock conflicts with concurrent inserts

D Trigger must use BEFORE INSERT to avoid deadlocks

💡 Hint

Concurrent transactions updating the same rows can cause deadlocks.

The trigger updates the accounts table row for each inserted transaction. If multiple transactions insert concurrently for the same account, they try to update the same row, causing deadlocks.

🧠 Conceptual

expert

Understanding limitations of AFTER INSERT triggers

Which statement about AFTER INSERT triggers in MySQL is TRUE?

A AFTER INSERT triggers execute after the row is inserted and can affect other tables but not the inserted row

B AFTER INSERT triggers execute before the row is inserted into the table

C AFTER INSERT triggers cannot be used to insert rows into other tables

D AFTER INSERT triggers can modify the row being inserted by changing NEW values

💡 Hint

Think about when AFTER INSERT triggers run and what NEW values represent.

AFTER INSERT triggers run after the row is inserted, so they cannot change the inserted row's NEW values. They can perform actions on other tables.