DELETE trigger in SQL - Mini Project: Build & Apply

Create a DELETE Trigger to Log Removed Records

📖 Scenario: You manage a small library database. When a book record is deleted, you want to keep a log of the deleted book's title and deletion time for future reference.

🎯 Goal: Build a DELETE trigger that automatically saves the title of the deleted book and the time it was deleted into a separate log table.

📋 What You'll Learn

Create a table called books with columns id (integer primary key) and title (text).

Create a table called deleted_books_log with columns book_title (text) and deleted_at (timestamp).

Create a DELETE trigger on the books table.

The trigger should insert the deleted book's title and current timestamp into deleted_books_log.

💡 Why This Matters

🌍 Real World

Logging deleted records helps keep track of important data changes and supports auditing in real-world databases.

💼 Career

Database administrators and developers often use triggers to automate tasks like logging, enforcing rules, and maintaining data integrity.

1

Create the books table

Create a table called books with columns id as an integer primary key and title as text.

SQL

# Create the books table with id and title columns
-- Your code here

Need a hint?

Use CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT);

2

Create the deleted_books_log table

Create a table called deleted_books_log with columns book_title as text and deleted_at as timestamp.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);
-- Create the deleted_books_log table with book_title and deleted_at columns
-- Your code here

Need a hint?

Use CREATE TABLE deleted_books_log (book_title TEXT, deleted_at TIMESTAMP);

3

Create the DELETE trigger function

Create a trigger function called log_deleted_book that inserts the deleted book's title and current timestamp into deleted_books_log. Use OLD.title to access the deleted row's title.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE deleted_books_log (
    book_title TEXT,
    deleted_at TIMESTAMP
);
-- Create the trigger function log_deleted_book
-- Your code here

Need a hint?

Use a trigger function with OLD.title and CURRENT_TIMESTAMP to insert into the log table.

4

Create the DELETE trigger on books

Create a DELETE trigger called after_delete_book on the books table that calls the log_deleted_book function after a row is deleted.

SQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE deleted_books_log (
    book_title TEXT,
    deleted_at TIMESTAMP
);

CREATE FUNCTION log_deleted_book() RETURNS trigger AS $$
BEGIN
    INSERT INTO deleted_books_log (book_title, deleted_at) VALUES (OLD.title, CURRENT_TIMESTAMP);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
-- Create the DELETE trigger after_delete_book on books
-- Your code here

Need a hint?

Use CREATE TRIGGER after_delete_book AFTER DELETE ON books FOR EACH ROW EXECUTE FUNCTION log_deleted_book();