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().

Practice

1. Which of the following is a best practice when creating MySQL triggers?

easy

A. Keep trigger logic simple to avoid performance issues.

B. Use triggers to replace all application logic.

C. Allow triggers to call themselves recursively without limits.

D. Write triggers that perform complex calculations on large datasets.

Solution

1. Step 1: Understand trigger performance impact

   Triggers run automatically on data changes, so complex logic can slow down database operations.

2. Step 2: Identify best practice for trigger design

   Keeping trigger logic simple helps maintain good performance and avoids unexpected delays.

3. Final Answer:

   Keep trigger logic simple to avoid performance issues. -> Option A

4. Quick Check:

   Simple trigger logic = Better performance [OK]

Hint: Keep triggers simple to avoid slowdowns [OK]

Common Mistakes:

• Trying to put all business logic inside triggers
• Allowing infinite recursion in triggers
• Using triggers for heavy data processing

2. Which syntax correctly defines a BEFORE INSERT trigger in MySQL?

easy

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

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

C. CREATE TRIGGER trg BEFORE UPDATE ON table_name FOR EACH ROW BEGIN ... END;

D. CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END;

Solution

1. Step 1: Identify trigger timing and event

   BEFORE INSERT triggers run before a new row is inserted.

2. Step 2: Match syntax to BEFORE INSERT event

   CREATE TRIGGER trg BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END; uses BEFORE INSERT, correct timing and event for this trigger.

3. Final Answer:

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

4. Quick Check:

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

Hint: BEFORE triggers run before data changes [OK]

Common Mistakes:

• Confusing BEFORE and AFTER keywords
• Using wrong event like UPDATE or DELETE
• Missing FOR EACH ROW clause

3. Given this trigger, what happens when a new row is inserted?

CREATE TRIGGER trg BEFORE INSERT ON employees FOR EACH ROW BEGIN SET NEW.salary = NEW.salary * 1.1; END;

medium

A. The trigger causes a syntax error.

B. The salary is increased by 10% before insertion.

C. The salary remains unchanged.

D. The insertion fails due to recursion.

Solution

1. Step 1: Analyze trigger action on BEFORE INSERT

   The trigger modifies the NEW.salary value by multiplying it by 1.1 before the row is inserted.

2. Step 2: Understand effect on inserted data

   The inserted row will have salary increased by 10% automatically.

3. Final Answer:

   The salary is increased by 10% before insertion. -> Option B

4. Quick Check:

   BEFORE INSERT modifies NEW.salary = increased salary [OK]

Hint: BEFORE triggers can modify NEW row data [OK]

Common Mistakes:

• Thinking triggers run after insertion
• Assuming salary stays the same
• Confusing NEW and OLD keywords

4. What is wrong with this trigger definition?

CREATE TRIGGER trg AFTER UPDATE ON employees FOR EACH ROW BEGIN UPDATE employees SET salary = salary * 1.05 WHERE id = NEW.id; END;

medium

A. It will not update any rows because NEW.id is invalid.

B. Syntax error due to missing DELIMITER statement.

C. Triggers cannot use UPDATE statements inside their body.

D. It causes infinite recursion by updating the same table inside the trigger.

Solution

1. Step 1: Identify trigger action on AFTER UPDATE

   The trigger updates the same table employees inside an AFTER UPDATE trigger.

2. Step 2: Understand recursion risk

   Updating the same table inside its own UPDATE trigger causes the trigger to call itself repeatedly, causing infinite recursion.

3. Final Answer:

   It causes infinite recursion by updating the same table inside the trigger. -> Option D

4. Quick Check:

   Trigger updating same table = recursion risk [OK]

Hint: Avoid updating same table inside its trigger [OK]

Common Mistakes:

• Ignoring recursion risk
• Assuming syntax error without DELIMITER
• Thinking triggers can't run UPDATE statements

5. You want to log every DELETE action on the orders table without slowing down the delete operation. Which trigger design is best?

hard

A. Use a BEFORE DELETE trigger that updates the orders table with a log message.

B. Use an AFTER DELETE trigger that updates the orders table itself.

C. Use an AFTER DELETE trigger that inserts a log record into a separate table.

D. Use a BEFORE DELETE trigger that performs complex calculations before deleting.

Solution

1. Step 1: Choose trigger timing for logging

   AFTER DELETE triggers run after the row is deleted, so they don't block the delete operation.

2. Step 2: Select action that avoids slowing delete

   Inserting a log record into a separate table is efficient and safe, avoiding recursion or delays.

3. Final Answer:

   Use an AFTER DELETE trigger that inserts a log record into a separate table. -> Option C

4. Quick Check:

   AFTER DELETE + separate log table = best practice [OK]

Hint: Log deletes AFTER action to avoid delays [OK]

Common Mistakes:

• Updating same table inside trigger causing recursion
• Using BEFORE DELETE for logging causing delays
• Doing heavy calculations inside triggers