DELETE trigger in SQL - Deep Dive

Overview - DELETE trigger

What is it?

A DELETE trigger is a special set of instructions in a database that automatically runs when a row is removed from a table. It helps you react to deletions by performing extra tasks like logging or cleaning related data. This happens without needing to change the application code that deletes the data. It ensures important actions happen exactly when data is deleted.

Why it matters

Without DELETE triggers, you would have to manually write extra code every time you delete data to keep things consistent or safe. This can lead to mistakes or missing important steps, causing data errors or loss. DELETE triggers automate these tasks, making databases more reliable and easier to maintain. They help protect data integrity and automate workflows tied to deletions.

Where it fits

Before learning DELETE triggers, you should understand basic SQL commands like DELETE and how tables store data. After this, you can learn about other types of triggers like INSERT and UPDATE triggers, and advanced database features like transaction control and stored procedures.

Mental Model

Core Idea

A DELETE trigger is an automatic helper that runs right after or before a row is deleted to handle extra tasks tied to that deletion.

Think of it like...

Imagine a security guard who watches a door and every time someone leaves a room, the guard automatically checks if they took everything they should and updates a logbook without anyone asking.

┌─────────────┐
│ DELETE Row  │
└──────┬──────┘
       │ triggers
┌──────▼──────┐
│ DELETE Trigger │
│ (runs code)   │
└──────┬──────┘
       │ performs
┌──────▼──────┐
│ Extra Tasks │
│ (log, clean)│
└─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic DELETE Command

Concept: Learn what the DELETE command does in SQL.

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

Result

The specified rows are removed from the table.

Knowing how DELETE works is essential because triggers respond to these deletions.

2

Foundation

What is a Trigger in Databases

Concept: Understand the general idea of triggers as automatic actions.

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

Result

Triggers run automatically when their event occurs.

Triggers let the database do extra work behind the scenes, improving reliability and consistency.

3

Intermediate

How DELETE Triggers Work

🤔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 before or after a row is deleted, depending on how they are defined.

A DELETE trigger can be set to run BEFORE the deletion happens to check or modify data, or AFTER the deletion to perform cleanup or logging. This timing affects what data the trigger can access and what it can do.

Result

Triggers run at the chosen time and can access old data being deleted.

Understanding trigger timing helps you decide when to perform checks or actions safely.

4

Intermediate

Accessing Deleted Data in Triggers

🤔Before reading on: do you think a DELETE trigger can see the data that was deleted? Commit to your answer.

Concept: DELETE triggers can access the data that is about to be or has been deleted using special references.

In SQL, DELETE triggers use a special table called 'deleted' which holds the rows being deleted. This lets the trigger read the old data to log it or use it in calculations.

Result

Triggers can safely access deleted rows for extra processing.

Knowing how to access deleted data inside triggers unlocks powerful automation possibilities.

5

Intermediate

Writing a Simple DELETE Trigger

Concept: Learn the syntax and structure of a DELETE trigger.

A DELETE trigger is created with a command like CREATE TRIGGER trigger_name BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END;. Inside, you write SQL statements that run when a row is deleted. For example, logging the deleted row's id.

Result

The trigger runs automatically on each row deletion.

Writing triggers lets you automate important tasks tied to data removal.

6

Advanced

Using DELETE Triggers for Cascading Deletes

🤔Before reading on: do you think DELETE triggers can replace foreign key cascading deletes? Commit to your answer.

Concept: DELETE triggers can be used to manually delete related rows in other tables, mimicking cascading deletes.

When a row is deleted, a DELETE trigger can delete related rows in other tables to keep data consistent. This is useful if cascading deletes are not supported or need custom logic.

Result

Related data is cleaned up automatically when a row is deleted.

Triggers provide flexible control over complex delete operations beyond built-in cascade rules.

7

Expert

Performance and Side Effects of DELETE Triggers

🤔Before reading on: do you think DELETE triggers always improve database performance? Commit to your answer.

Concept: DELETE triggers add overhead and can cause unexpected side effects if not carefully designed.

Triggers run extra code on every delete, which can slow down operations if complex. They can also cause cascading triggers or recursive calls if triggers delete rows themselves. Proper design and testing are needed to avoid performance hits and infinite loops.

Result

Triggers work but may impact speed and cause complex behaviors.

Knowing the costs and risks of triggers helps you design safe, efficient database logic.

Under the Hood

When a DELETE command runs, the database engine identifies the rows to remove. If a DELETE trigger exists, the engine pauses the deletion to run the trigger code. For BEFORE DELETE triggers, the trigger runs before the row is removed, allowing checks or modifications. For AFTER DELETE triggers, the row is deleted first, then the trigger runs. The trigger accesses the 'deleted' pseudo-table containing the rows affected. After the trigger finishes, the deletion completes or rolls back if errors occur.

Why designed this way?

Triggers were designed to automate database rules and maintain integrity without relying on application code. The BEFORE and AFTER options give flexibility to validate or react to changes at the right time. Using pseudo-tables like 'deleted' allows triggers to access affected data safely. This design balances automation with control and safety.

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

Myth Busters - 4 Common Misconceptions

Quick: Does a DELETE trigger prevent the row from being deleted if it raises an error? Commit to yes or no.

Common Belief: If a DELETE trigger raises an error, the row still gets deleted.

Reality: If a DELETE trigger raises an error, the entire DELETE operation rolls back and the row is not deleted.

Why it matters: Assuming errors don't stop deletion can cause data loss or inconsistent states if triggers are used for validation.

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 usually run once per row deleted, allowing row-level processing.

Why it matters: Misunderstanding this can lead to performance issues or incorrect logic if you expect only one trigger run.

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

Common Belief: DELETE triggers can access new data being inserted or updated.

Reality: DELETE triggers only access data being deleted, not new or updated data.

Why it matters: Confusing trigger types can cause bugs when trying to access data that isn't available in the trigger context.

Quick: Can DELETE triggers cause infinite loops by deleting rows inside themselves? Commit to yes or no.

Common Belief: DELETE triggers cannot cause infinite loops because the database prevents it.

Reality: DELETE triggers can cause infinite loops if they delete rows that fire the same trigger again, unless carefully controlled.

Why it matters: Ignoring this can crash the database or cause severe performance problems.

Expert Zone

1

DELETE triggers can be statement-level or row-level, and this choice affects performance and logic complexity.

2

Some databases support disabling triggers temporarily, which is useful during bulk deletes but can risk data integrity.

3

Triggers can interact with transactions, so understanding commit and rollback behavior is critical to avoid partial data changes.

When NOT to use

Avoid DELETE triggers when simple foreign key cascading deletes suffice, or when performance is critical and triggers add unacceptable overhead. Instead, use built-in cascade options or handle deletions in application logic for complex workflows.

Production Patterns

In production, DELETE triggers are often used for audit logging, enforcing business rules, cleaning related data, and maintaining summary tables. They are combined with transactions and error handling to ensure data consistency and traceability.

Connections

Foreign Key Cascading Deletes

DELETE triggers can implement or extend cascading deletes manually.

Understanding DELETE triggers helps grasp how databases maintain referential integrity beyond built-in cascade options.

Event-Driven Programming

DELETE triggers are a form of event-driven automation inside databases.

Knowing triggers connects database operations to broader programming patterns where code reacts automatically to events.

Audit Logging in Security

DELETE triggers often implement audit logs to track data removal for security and compliance.

Learning DELETE triggers reveals how databases support security practices by automatically recording sensitive actions.

Common Pitfalls

#1 Assuming DELETE triggers run once per statement, not per row.

Wrong approach:

CREATE TRIGGER trg_delete AFTER DELETE ON employees FOR EACH STATEMENT BEGIN INSERT INTO log ... END;

Correct approach:

CREATE TRIGGER trg_delete AFTER DELETE ON employees FOR EACH ROW BEGIN INSERT INTO log ... END;

Root cause:

Confusing statement-level and row-level triggers leads to missing expected repeated actions.

#2 Writing a DELETE trigger that deletes rows in the same table without control, causing infinite recursion.

Wrong approach:

CREATE TRIGGER trg_delete BEFORE DELETE ON employees FOR EACH ROW BEGIN DELETE FROM employees WHERE manager_id = OLD.id; END;

Correct approach:

CREATE TRIGGER trg_delete BEFORE DELETE ON employees FOR EACH ROW BEGIN DELETE FROM employees WHERE manager_id = OLD.id AND id != OLD.id; END;

Root cause:

Not preventing the trigger from deleting the same row repeatedly causes infinite loops.

#3 Trying to access new data inside a DELETE trigger.

Wrong approach:

SELECT * FROM inserted; -- inside a DELETE trigger

Correct approach:

SELECT * FROM deleted; -- inside a DELETE trigger

Root cause:

Misunderstanding that DELETE triggers only have access to 'deleted' pseudo-table, not 'inserted'.

Key Takeaways

DELETE triggers automatically run code when rows are deleted, helping automate important tasks.

They can run before or after deletion, affecting what data and actions are possible.

Triggers access deleted rows through a special 'deleted' table, enabling logging and cleanup.

Misusing triggers can cause performance problems or infinite loops, so careful design is essential.

DELETE triggers connect database operations to broader programming and security practices.