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.