DELETE trigger in SQL - 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.

SQL

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

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

ASELECT

BUPDATE

CINSERT

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Using INSERT or UPDATE instead of DELETE as the trigger event.

Confusing BEFORE and AFTER timing.

The trigger must activate before DELETE to run when a row is deleted.

2fill in blank

medium

Complete the code to reference the old row's id in the DELETE trigger.

SQL

CREATE TRIGGER log_delete BEFORE DELETE ON employees FOR EACH ROW BEGIN INSERT INTO deleted_log (emp_id) VALUES ([1].id); END;

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

AOLD

BROW

CNEW

DCURRENT

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW instead of OLD to reference the deleted row.

Using CURRENT or ROW which are not valid in this context.

In DELETE triggers, OLD refers to the row being deleted.

3fill in blank

hard

Fix the error in the DELETE trigger code by choosing the correct timing keyword.

SQL

CREATE TRIGGER audit_delete [1] DELETE ON employees FOR EACH ROW BEGIN INSERT INTO audit_log VALUES (OLD.id, NOW()); END;

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

AAFTER

BBEFORE

CDURING

DON

Attempts:

3 left

💡 Hint

Common Mistakes

Using BEFORE instead of AFTER for audit triggers.

Using invalid timing keywords like DURING or ON.

For logging after a row is deleted, the trigger timing should be AFTER DELETE.

4fill in blank

hard

Fill both blanks to create a DELETE trigger that prevents deletion if the employee is a manager.

SQL

CREATE TRIGGER prevent_manager_delete BEFORE [1] ON employees FOR EACH ROW BEGIN IF OLD.role = 'manager' THEN SIGNAL SQLSTATE '[2]' SET MESSAGE_TEXT = 'Cannot delete a manager'; END IF; END;

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

ADELETE

B45000

CINSERT

D23000

Attempts:

3 left

💡 Hint

Common Mistakes

Using INSERT instead of DELETE as the trigger event.

Using SQLSTATE '23000' which is for integrity constraint violations.

The trigger must be BEFORE DELETE to check before deletion. SQLSTATE '45000' is a generic user-defined error code.

5fill in blank

hard

Fill all three blanks to create a DELETE trigger that logs deleted employee name and deletion time.

SQL

CREATE TRIGGER log_employee_delete AFTER [1] ON employees FOR EACH ROW BEGIN INSERT INTO delete_log (name, deleted_at) VALUES ([2].name, [3]()); END;

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

ADELETE

BOLD

CNOW

DNEW

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW instead of OLD to access deleted data.

Using BEFORE instead of AFTER timing.

Forgetting parentheses on NOW() function.

The trigger runs AFTER DELETE. OLD refers to the deleted row. NOW() returns the current timestamp.