DELETE triggers in MySQL - Deep Dive

Overview - DELETE triggers

What is it?

A DELETE trigger is a special set of instructions in a database that automatically runs when a row is deleted from a table. It helps you perform extra actions like cleaning up related data or logging changes without writing extra code every time. This happens right after or before the deletion happens. It works silently in the background to keep your data consistent and safe.

Why it matters

Without DELETE triggers, you would have to manually write extra code every time you delete data to handle related tasks like removing connected records or keeping logs. This can lead to mistakes, forgotten steps, or inconsistent data. DELETE triggers automate these tasks, making your database more reliable and easier to maintain, especially when many deletions happen.

Where it fits

Before learning DELETE triggers, you should understand basic SQL commands like DELETE and how tables relate to each other. After mastering DELETE triggers, you can learn about other types of triggers like INSERT and UPDATE triggers, and advanced database features like stored procedures and transaction management.

Mental Model

Core Idea

A DELETE trigger is like an automatic helper that steps in exactly when you remove data, to do extra jobs that keep your database tidy and consistent.

Think of it like...

Imagine you have a library where removing a book also needs you to remove its reservation slips and notify readers. A DELETE trigger is like a librarian assistant who automatically does these extra tasks whenever a book is taken out, so you don’t forget anything.

┌─────────────┐       DELETE command       ┌───────────────┐
│ User issues │ ─────────────────────────▶ │ Database      │
│ DELETE row  │                           │ Table         │
└─────────────┘                           └───────────────┘
                                             │
                                             ▼
                                    ┌───────────────────┐
                                    │ DELETE Trigger     │
                                    │ (Before or After)  │
                                    └───────────────────┘
                                             │
                                             ▼
                                    ┌───────────────────┐
                                    │ Additional Actions │
                                    │ (e.g., cleanup)    │
                                    └───────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic DELETE Command

Concept: Learn what the DELETE command does in SQL and how it removes rows from a table.

The DELETE command removes one or more rows from a table based on a condition. For example, DELETE FROM users WHERE id = 5; removes the user with id 5. This command changes the data stored in the table.

Result

The specified rows are removed from the table permanently.

Knowing how DELETE works is essential because triggers respond directly to this action.

2

Foundation

What Are Database Triggers?

Concept: Introduce the idea of triggers as automatic actions tied to database events.

A trigger is a set of instructions that runs automatically when certain events happen in the database, like inserting, updating, or deleting data. They help automate tasks and keep data consistent without manual intervention.

Result

Triggers run automatically when their event occurs, performing predefined tasks.

Understanding triggers as automatic helpers prepares you to see how DELETE triggers fit in.

3

Intermediate

How DELETE Triggers Work in MySQL

🤔Before reading on: do you think DELETE triggers run before or after the row is deleted? Commit to your answer.

Concept: DELETE triggers can run either before or after a row is deleted, allowing different types of actions.

In MySQL, you can create DELETE triggers that run BEFORE or AFTER a row is deleted. BEFORE DELETE triggers can check or modify data before deletion, while AFTER DELETE triggers can perform cleanup or logging after the row is gone.

Result

Triggers execute automatically at the chosen time relative to the deletion.

Knowing the timing of triggers helps you decide when to perform checks or cleanup.

4

Intermediate

Creating a Simple DELETE Trigger

🤔Before reading on: do you think a DELETE trigger can prevent a deletion? Commit to your answer.

Concept: Learn the syntax to create a DELETE trigger and understand its capabilities.

Example: CREATE TRIGGER before_user_delete BEFORE DELETE ON users FOR EACH ROW BEGIN INSERT INTO audit_log(action, user_id) VALUES('delete', OLD.id); END; This trigger logs the deletion before it happens. Note: DELETE triggers cannot stop the deletion but can raise errors to prevent it.

Result

When a user row is deleted, a log entry is automatically created.

Seeing a real trigger example clarifies how triggers automate related tasks.

5

Intermediate

Using OLD and NEW Keywords in DELETE Triggers

Concept: Understand how to access the data being deleted inside the trigger.

In DELETE triggers, the OLD keyword refers to the row data before deletion. NEW is not available because the row is being removed. You can use OLD.column_name to get values from the deleted row for logging or cleanup.

Result

You can reference the deleted row's data inside the trigger code.

Knowing how to access deleted data lets you perform meaningful actions like logging or cascading deletes.

6

Advanced

Handling Cascading Deletes with DELETE Triggers

🤔Before reading on: do you think foreign keys alone handle all related deletions? Commit to your answer.

Concept: Learn how DELETE triggers can complement or replace foreign key cascading deletes for complex cleanup.

Foreign keys with ON DELETE CASCADE automatically delete related rows. However, sometimes you need custom logic, like deleting files or logging. DELETE triggers let you write this custom cleanup code that runs whenever a row is deleted.

Result

Related data and side effects are handled automatically and correctly.

Understanding when to use triggers vs foreign keys helps maintain data integrity and business rules.

7

Expert

Limitations and Performance Considerations of DELETE Triggers

🤔Before reading on: do you think DELETE triggers can slow down bulk deletes significantly? Commit to your answer.

Concept: Explore how triggers affect performance and what to watch out for in production.

DELETE triggers run for each row deleted, which can slow down large delete operations. Complex trigger logic can cause unexpected delays or deadlocks. Also, triggers cannot call COMMIT or ROLLBACK, limiting transaction control. Careful design and testing are needed for production use.

Result

Triggers add overhead and complexity that must be managed carefully.

Knowing these limits prevents performance problems and helps design efficient triggers.

Under the Hood

When a DELETE command runs, MySQL checks if any DELETE triggers exist on the affected table. If a BEFORE DELETE trigger exists, it runs once for each row before the row is removed. After the row is deleted, any AFTER DELETE triggers run. The OLD keyword provides access to the deleted row's data during trigger execution. The trigger code runs inside the same transaction as the DELETE, so if the trigger causes an error, the whole operation can roll back.

Why designed this way?

Triggers were designed to automate repetitive or critical tasks tied to data changes, reducing human error and code duplication. The BEFORE and AFTER timing options give flexibility to validate or clean up data. Running triggers per row ensures precise control but trades off performance for granularity. This design balances power and safety in data management.

┌───────────────┐
│ DELETE command│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ BEFORE DELETE │
│ Trigger runs  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Row Deleted   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ AFTER DELETE  │
│ Trigger runs  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can a DELETE trigger prevent the deletion from happening? Commit to yes or no.

Common Belief: DELETE triggers can stop a row from being deleted if certain conditions are met.

Reality: DELETE triggers cannot directly prevent deletion; they can only raise errors to abort the transaction.

Why it matters: Thinking triggers can silently block deletes leads to wrong assumptions about data safety and may cause unexpected errors.

Quick: Do DELETE triggers have access to the new data being inserted? Commit to yes or no.

Common Belief: DELETE triggers can access both old and new row data like UPDATE triggers.

Reality: DELETE triggers only have access to OLD data because the row is being removed; NEW data does not exist.

Why it matters: Misusing NEW in DELETE triggers causes errors and confusion about what data is available.

Quick: Do DELETE triggers run once per statement or once per row? Commit to your answer.

Common Belief: DELETE triggers run once per DELETE statement regardless of how many rows are deleted.

Reality: DELETE triggers run once for each row deleted, not just once per statement.

Why it matters: Underestimating trigger executions can cause performance issues and unexpected side effects.

Quick: Are DELETE triggers always faster than manual cleanup code? Commit to yes or no.

Common Belief: Using DELETE triggers always improves performance compared to manual cleanup.

Reality: DELETE triggers add overhead and can slow down bulk deletes if the trigger logic is complex.

Why it matters: Assuming triggers are always faster can lead to slow applications and poor user experience.

Expert Zone

1

DELETE triggers execute within the same transaction as the DELETE command, so any error in the trigger rolls back the entire operation.

2

Triggers cannot call transaction control commands like COMMIT or ROLLBACK, limiting their ability to manage transactions independently.

3

In MySQL, triggers are limited to one per event per table, so complex logic may require chaining through stored procedures.

When NOT to use

Avoid DELETE triggers when you need to delete large amounts of data quickly without overhead. Instead, use foreign key ON DELETE CASCADE for simple related deletions or batch scripts for complex cleanup. Also, avoid triggers if you need fine-grained transaction control inside the trigger.

Production Patterns

In production, DELETE triggers are often used for audit logging, enforcing business rules, and cleaning up related data that foreign keys cannot handle. They are combined with careful indexing and transaction management to balance data integrity and performance.

Connections

Foreign Key Constraints

DELETE triggers often complement or replace foreign key ON DELETE CASCADE rules.

Understanding foreign keys helps decide when to use triggers for custom cleanup beyond simple cascading deletes.

Transaction Management

DELETE triggers run inside transactions and affect commit or rollback behavior.

Knowing how transactions work clarifies how trigger errors can undo deletions and maintain data consistency.

Event-Driven Programming

Triggers are a form of event-driven code that reacts automatically to database events.

Recognizing triggers as event handlers connects database concepts to broader programming patterns.

Common Pitfalls

#1 Trying to use NEW keyword in a DELETE trigger.

Wrong approach:

CREATE TRIGGER trg_delete BEFORE DELETE ON users FOR EACH ROW BEGIN SET NEW.name = 'test'; END;

Correct approach:

CREATE TRIGGER trg_delete BEFORE DELETE ON users FOR EACH ROW BEGIN -- Use OLD to access data, NEW is not available END;

Root cause:

Misunderstanding that NEW data does not exist during deletion because the row is being removed.

#2 Assuming DELETE triggers run once per statement.

Wrong approach:

Writing trigger logic that expects to run only once regardless of rows deleted.

Correct approach:

Design triggers knowing they run once per row deleted, e.g., handle multiple executions safely.

Root cause:

Confusing statement-level triggers with row-level triggers in MySQL.

#3 Writing heavy logic inside DELETE triggers causing slow deletes.

Wrong approach:

CREATE TRIGGER trg_delete AFTER DELETE ON orders FOR EACH ROW BEGIN CALL complex_cleanup_procedure(OLD.id); END;

Correct approach:

Perform heavy cleanup asynchronously outside the trigger or batch process to avoid slowing deletes.

Root cause:

Not considering performance impact of per-row trigger execution.

Key Takeaways

DELETE triggers automatically run code before or after a row is deleted, helping automate cleanup and logging.

They have access to the deleted row's data through the OLD keyword but cannot access NEW data.

Triggers run once per row deleted, which can impact performance on large deletes.

They cannot directly prevent deletions but can raise errors to abort the operation.

Understanding when and how to use DELETE triggers alongside foreign keys and transactions is key to maintaining data integrity and performance.

Practice

1. What is the main purpose of a DELETE trigger in MySQL?

easy

A. To automatically run code when a row is deleted

B. To insert new rows into a table

C. To update rows before selecting them

D. To create new tables dynamically

Solution

1. Step 1: Understand what triggers do

   Triggers run automatically when certain actions happen on a table, like DELETE.

2. Step 2: Identify the purpose of DELETE triggers

   DELETE triggers specifically run when rows are deleted to perform extra tasks.

3. Final Answer:

   To automatically run code when a row is deleted -> Option A

4. Quick Check:

   DELETE triggers = automatic code on row deletion [OK]

Hint: Triggers run automatically on data changes like DELETE [OK]

Common Mistakes:

• Confusing DELETE triggers with INSERT or UPDATE triggers
• Thinking triggers create or modify tables
• Believing triggers run only manually

2. Which of the following is the correct syntax to create a BEFORE DELETE trigger in MySQL?

easy

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

B. CREATE TRIGGER trg AFTER DELETE 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 FROM table_name BEGIN ... END;

Solution

1. Step 1: Recall the correct trigger syntax

   MySQL triggers use CREATE TRIGGER name BEFORE|AFTER event ON table FOR EACH ROW BEGIN ... END;

2. Step 2: Match syntax for BEFORE DELETE

   CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END; matches this exactly with BEFORE DELETE and correct structure.

3. Final Answer:

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

4. Quick Check:

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

Hint: Syntax: CREATE TRIGGER name BEFORE|AFTER DELETE ON table [OK]

Common Mistakes:

• Using AFTER instead of BEFORE when asked
• Wrong event like UPDATE instead of DELETE
• Missing FOR EACH ROW clause

3. Given this trigger and table:

CREATE TABLE users (id INT, name VARCHAR(20));
CREATE TRIGGER log_delete AFTER DELETE ON users FOR EACH ROW BEGIN
INSERT INTO deleted_log VALUES (OLD.id, OLD.name);
END;

What happens when DELETE FROM users WHERE id=1; is executed?

medium

A. The row with id=1 is deleted but nothing is logged

B. The row with id=1 is deleted and its data is inserted into deleted_log

C. The DELETE statement fails with a syntax error

D. The row with id=1 is not deleted because of the trigger

Solution

1. Step 1: Understand AFTER DELETE trigger behavior

   The trigger runs after a row is deleted and inserts OLD row data into deleted_log.

2. Step 2: Analyze the DELETE command effect

   Deleting id=1 removes that row and the trigger logs its data into deleted_log.

3. Final Answer:

   The row with id=1 is deleted and its data is inserted into deleted_log -> Option B

4. Quick Check:

   AFTER DELETE trigger logs deleted row [OK]

Hint: AFTER DELETE triggers run after row removal to log data [OK]

Common Mistakes:

• Thinking trigger prevents deletion
• Assuming no logging happens
• Confusing OLD with NEW in DELETE triggers

4. Identify the error in this trigger definition:

CREATE TRIGGER trg BEFORE DELETE ON users
BEGIN
INSERT INTO deleted_log VALUES (OLD.id, OLD.name);
END;

medium

A. Using NEW in a DELETE trigger is invalid

B. BEFORE DELETE triggers cannot modify data

C. Trigger body must be a single statement without BEGIN...END

D. Missing FOR EACH ROW clause

Solution

1. Step 1: Check trigger syntax requirements

   MySQL requires FOR EACH ROW clause in row-level triggers.

2. Step 2: Identify missing FOR EACH ROW

   The trigger lacks FOR EACH ROW, causing syntax error.

3. Final Answer:

   Missing FOR EACH ROW clause -> Option D

4. Quick Check:

   FOR EACH ROW is mandatory in triggers [OK]

Hint: Always include FOR EACH ROW in row-level triggers [OK]

Common Mistakes:

• Using NEW in DELETE triggers (allowed only OLD)
• Omitting FOR EACH ROW clause
• Incorrect BEGIN...END usage

5. You want to prevent deletion of rows in the orders table if the order status is 'shipped'. Which DELETE trigger logic is correct?

hard

A. CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF NEW.status = 'shipped' THEN DELETE FROM orders WHERE id = OLD.id; END IF; END;

B. CREATE TRIGGER trg AFTER DELETE ON orders FOR EACH ROW BEGIN IF NEW.status = 'shipped' THEN ROLLBACK; END IF; END;

C. CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders'; END IF; END;

D. CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN UPDATE orders SET status = 'cancelled' WHERE id = OLD.id; END IF; END;

Solution

1. Step 1: Understand how to prevent deletion

   Use BEFORE DELETE trigger and SIGNAL to raise an error and stop deletion.

2. Step 2: Analyze each option's logic

   CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders'; END IF; END; correctly uses BEFORE DELETE, checks OLD.status, and signals error to prevent deletion.

3. Final Answer:

   CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders'; END IF; END; -> Option C

4. Quick Check:

   Use SIGNAL in BEFORE DELETE to block deletion [OK]

Hint: Use SIGNAL in BEFORE DELETE to stop unwanted deletions [OK]

Common Mistakes:

• Using AFTER DELETE to prevent deletion (too late)
• Using NEW in DELETE triggers (only OLD is valid)
• Trying to delete inside a DELETE trigger causing recursion