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.

Practice

1. What does an AFTER INSERT trigger do in MySQL?

easy

A. It deletes rows after insertion.

B. It runs before a row is inserted into a table.

C. It runs automatically after a new row is added to a table.

D. It prevents rows from being inserted.

Solution

1. Step 1: Understand trigger timing

   An AFTER INSERT trigger runs after a new row is successfully inserted into a table.

2. Step 2: Identify trigger purpose

   This trigger is used to perform actions like logging or updating related tables after insertion.

3. Final Answer:

   It runs automatically after a new row is added to a table. -> Option C

4. Quick Check:

   AFTER INSERT trigger = runs after insert [OK]

Hint: AFTER INSERT triggers run only after data is added [OK]

Common Mistakes:

• Confusing AFTER with BEFORE triggers
• Thinking triggers delete or block inserts
• Assuming triggers run before insertion

2. Which of the following is the correct syntax to create an AFTER INSERT trigger in MySQL?

easy

A. CREATE TRIGGER trg AFTER UPDATE ON table_name FOR EACH ROW BEGIN ... END;

B. CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;

C. CREATE TRIGGER trg AFTER DELETE ON table_name FOR EACH ROW BEGIN ... END;

D. CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END;

Solution

1. Step 1: Identify trigger timing and event

   The question asks for an AFTER INSERT trigger, so the syntax must include AFTER INSERT.

2. Step 2: Match syntax structure

   The correct syntax is CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END;.

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END; -> Option D

4. Quick Check:

   AFTER INSERT trigger syntax = CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END; [OK]

Hint: AFTER INSERT triggers use 'AFTER INSERT' in syntax [OK]

Common Mistakes:

• Using BEFORE instead of AFTER
• Using wrong event like UPDATE or DELETE
• Omitting FOR EACH ROW clause

3. Given this trigger and table:

CREATE TABLE users (id INT, name VARCHAR(20));
CREATE TABLE logs (msg VARCHAR(50));
DELIMITER $$
CREATE TRIGGER after_user_insert AFTER INSERT ON users
FOR EACH ROW BEGIN
  INSERT INTO logs VALUES (CONCAT('User ', NEW.name, ' added'));
END$$
DELIMITER ;

INSERT INTO users VALUES (1, 'Alice');
SELECT * FROM logs;

What will the SELECT query return?

medium

A. [{"msg": "User Alice added"}]

B. [{"msg": "User 1 added"}]

C. Empty result set

D. Syntax error

Solution

1. Step 1: Understand trigger action on insert

   The trigger inserts a log message using NEW.name after a user is added.

2. Step 2: Check inserted data and log content

   Inserting (1, 'Alice') triggers log insertion with message 'User Alice added'.

3. Final Answer:

   [{"msg": "User Alice added"}] -> Option A

4. Quick Check:

   NEW.name = 'Alice' logged [OK]

Hint: NEW accesses inserted row data inside AFTER INSERT [OK]

Common Mistakes:

• Using NEW.id instead of NEW.name for message
• Expecting no logs created
• Confusing syntax causing errors

4. You wrote this trigger but get an error:

CREATE TRIGGER trg AFTER INSERT ON users
FOR EACH ROW
INSERT INTO logs VALUES ('New user added');
INSERT INTO logs VALUES ('Trigger fired');

What is the problem?

medium

A. Missing BEGIN and END block for multiple statements

B. AFTER INSERT triggers cannot insert into other tables

C. FOR EACH ROW is not allowed in AFTER triggers

D. Trigger name cannot be 'trg'

Solution

1. Step 1: Check trigger body syntax

   When a trigger has more than one statement or uses procedural code, it needs BEGIN ... END block.

2. Step 2: Identify missing block

   The trigger has multiple statements but lacks BEGIN ... END, which is required for clarity and to avoid errors.

3. Final Answer:

   Missing BEGIN and END block for multiple statements -> Option A

4. Quick Check:

   Triggers need BEGIN...END for statements [OK]

Hint: Always use BEGIN...END for trigger statements [OK]

Common Mistakes:

• Assuming single statement triggers don't need BEGIN...END
• Thinking AFTER triggers can't modify other tables
• Misunderstanding FOR EACH ROW usage

5. You want to create an AFTER INSERT trigger on a sales table that updates a summary table with the total sales amount. Which approach is correct?

hard

A. Use OLD.amount in the trigger to subtract from the summary table's total.

B. Use NEW.amount in the trigger to add to the summary table's total after each insert.

C. Use a BEFORE INSERT trigger to update the summary table before inserting sales.

D. Use a DELETE trigger to update the summary table after sales are removed.

Solution

1. Step 1: Identify correct trigger timing for updating after insert

   AFTER INSERT triggers run after new rows are added, so they can safely update summary data using inserted values.

2. Step 2: Use NEW to access inserted row data

   Inside the trigger, NEW.amount gives the inserted sale amount to add to the summary.

3. Final Answer:

   Use NEW.amount in the trigger to add to the summary table's total after each insert. -> Option B

4. Quick Check:

   AFTER INSERT + NEW = update summary correctly [OK]

Hint: Use NEW in AFTER INSERT to access inserted data [OK]

Common Mistakes:

• Using OLD in INSERT triggers (OLD is NULL)
• Using BEFORE INSERT instead of AFTER INSERT
• Using DELETE triggers for insert updates