Trigger best practices and limitations in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger that runs before an insert on the 'orders' table.

MySQL

CREATE TRIGGER before_order_insert [1] INSERT ON orders FOR EACH ROW BEGIN END;

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

ABEFORE

BAFTER

CINSTEAD OF

DON

Attempts:

3 left

💡 Hint

Common Mistakes

Using AFTER instead of BEFORE causes the trigger to run too late.

Using INSTEAD OF is not supported in MySQL triggers.

The trigger must be defined as BEFORE INSERT to run before inserting a row.

2fill in blank

medium

Complete the code to reference the new row's 'quantity' value inside the trigger.

MySQL

SET @new_quantity = NEW.[1];

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

Anew_quantity

Bold_quantity

Cqty

Dquantity

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD instead of NEW for insert triggers.

Using a wrong column name like 'qty' if it doesn't exist.

Inside triggers, NEW.column_name refers to the new row's column. Here, 'quantity' is the correct column name.

3fill in blank

hard

Fix the error in the trigger definition by choosing the correct timing keyword.

MySQL

CREATE TRIGGER update_stock [1] UPDATE ON products FOR EACH ROW BEGIN END;

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

AAFTER

BON

CBEFORE

DDURING

Attempts:

3 left

💡 Hint

Common Mistakes

Using DURING or ON which are invalid keywords.

Using AFTER when you need to modify data before update.

BEFORE UPDATE triggers run before the update happens, allowing changes to NEW values.

4fill in blank

hard

Fill both blanks to create a trigger that prevents deleting from 'users' table.

MySQL

CREATE TRIGGER prevent_user_delete [1] [2] ON users FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete not allowed'; END;

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

ABEFORE

BAFTER

CDELETE

DINSERT

Attempts:

3 left

💡 Hint

Common Mistakes

Using AFTER instead of BEFORE, which is too late to stop deletion.

Using INSERT event instead of DELETE.

To prevent deletion, use a BEFORE DELETE trigger that raises an error.

5fill in blank

hard

Fill all three blanks to create a trigger that logs inserts with timestamp and user ID.

MySQL

CREATE TRIGGER log_insert [1] INSERT ON orders FOR EACH ROW BEGIN INSERT INTO audit_log (action, user_id, action_time) VALUES ([2], NEW.user_id, [3]); END;

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

ABEFORE

B'INSERT'

CNOW()

DAFTER

Attempts:

3 left

💡 Hint

Common Mistakes

Using BEFORE instead of AFTER, which may log before insertion.

Forgetting to quote the action string 'INSERT'.

The trigger runs AFTER INSERT to log the action. The action is the string 'INSERT' and the current time is NOW().