AFTER INSERT triggers in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an AFTER INSERT trigger that logs new entries.

MySQL

CREATE TRIGGER log_new_entry AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO logs (user_id) VALUES ([1]); END;

Drag options to blanks, or click blank then click option'

ANEW.name

BOLD.id

CNEW.id

DOLD.name

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD instead of NEW causes errors because OLD is not available in AFTER INSERT triggers.

Referencing a column that does not exist in the table.

The NEW keyword refers to the inserted row. We use NEW.id to get the new user's ID.

2fill in blank

medium

Complete the code to create an AFTER INSERT trigger that updates a counter in another table.

MySQL

CREATE TRIGGER update_count AFTER INSERT ON orders FOR EACH ROW BEGIN UPDATE stats SET order_count = order_count + 1 WHERE id = [1]; END;

Drag options to blanks, or click blank then click option'

ANEW.id

B1

COLD.id

DNEW.user_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW.id or OLD.id when the stats table expects a fixed id.

Trying to use OLD in an AFTER INSERT trigger.

We update the row with id = 1 in the stats table to increment the order count.

3fill in blank

hard

Fix the error in the trigger code to correctly insert a log entry after a new product is added.

MySQL

CREATE TRIGGER product_log AFTER INSERT ON products FOR EACH ROW BEGIN INSERT INTO product_logs (product_name) VALUES ([1]); END;

Drag options to blanks, or click blank then click option'

ANEW.name

BOLD.name

CNEW.product_name

DOLD.product_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD instead of NEW causes errors.

Using the wrong column name like product_name if the column is named name.

Use NEW.name to access the inserted product's name. OLD is not available in AFTER INSERT triggers.

4fill in blank

hard

Fill both blanks to create an AFTER INSERT trigger that copies the new user's email and username into a separate table.

MySQL

CREATE TRIGGER copy_user_info AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO user_info (email, username) VALUES ([1], [2]); END;

Drag options to blanks, or click blank then click option'

ANEW.email

BNEW.username

COLD.email

DOLD.username

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD values in an AFTER INSERT trigger.

Mixing up email and username columns.

Use NEW.email and NEW.username to get the inserted user's data.

5fill in blank

hard

Fill all three blanks to create an AFTER INSERT trigger that inserts the new order's id, user_id, and total into the orders_log table.

MySQL

CREATE TRIGGER log_order AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO orders_log (order_id, user_id, total_amount) VALUES ([1], [2], [3]); END;

Drag options to blanks, or click blank then click option'

ANEW.id

BNEW.user_id

CNEW.total

DOLD.id

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD values in an AFTER INSERT trigger.

Using wrong column names or missing one of the required columns.

Use NEW.id, NEW.user_id, and NEW.total to access the inserted order's data.

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