DELETE triggers in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a DELETE trigger that activates before a row is deleted.

MySQL

CREATE TRIGGER before_delete_example BEFORE DELETE ON employees FOR EACH ROW BEGIN [1]; END;

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

ASET @old_salary = OLD.salary

BINSERT INTO log_table VALUES ('deleted')

CUPDATE employees SET salary = 0

DSELECT * FROM employees

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW keyword in DELETE triggers (NEW is for INSERT/UPDATE).

Trying to SELECT inside a trigger without INTO clause.

The trigger uses OLD to access the row being deleted. 'SET @old_salary = OLD.salary' stores the salary before deletion.

2fill in blank

medium

Complete the code to log the deleted employee's ID into a log table.

MySQL

CREATE TRIGGER log_delete_employee AFTER DELETE ON employees FOR EACH ROW BEGIN INSERT INTO delete_log(employee_id) VALUES([1]); END;

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

ANEW.id

Bdeleted_id

Cid

DOLD.id

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW.id which is invalid in DELETE triggers.

Using column name without OLD or NEW prefix.

In DELETE triggers, OLD refers to the row being deleted. So OLD.id gives the employee's ID before deletion.

3fill in blank

hard

Fix the error in the trigger code to prevent syntax issues.

MySQL

CREATE TRIGGER delete_check BEFORE DELETE ON orders FOR EACH ROW BEGIN IF [1] THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped order'; END IF; END;

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

ANEW.status = 'shipped'

BOLD.status = 'shipped'

Cstatus = 'shipped'

DOLD.status == 'shipped'

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW in DELETE triggers.

Using '==' instead of '=' for comparison.

Not prefixing column with OLD.

Use OLD.status to check the status of the row being deleted. Use single '=' for comparison in SQL.

4fill in blank

hard

Fill both blanks to create a trigger that prevents deletion if quantity is greater than zero.

MySQL

CREATE TRIGGER prevent_delete BEFORE DELETE ON inventory FOR EACH ROW BEGIN IF [1] [2] 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete item with stock'; END IF; END;

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

AOLD.quantity

BNEW.quantity

C>

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW in DELETE triggers.

Using '<=' instead of '>' causing wrong logic.

Use OLD.quantity to check the current row's quantity. Use '>' to check if quantity is greater than zero.

5fill in blank

hard

Fill all three blanks to create a DELETE trigger that archives deleted rows.

MySQL

CREATE TRIGGER archive_delete AFTER DELETE ON products FOR EACH ROW BEGIN INSERT INTO archive (product_id, name, deleted_at) VALUES ([1], [2], [3]); END;

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

AOLD.product_id

BOLD.name

CNOW()

DNEW.name

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW in DELETE triggers.

Forgetting to use NOW() for timestamp.

Use OLD to access deleted row's columns. Use NOW() to record deletion time.