INSTEAD OF trigger for views in PostgreSQL - Mini Project: Build & Apply

Using INSTEAD OF Trigger for Views in PostgreSQL

📖 Scenario: You are managing a bookstore database. You want to allow users to update book prices through a view that combines book details and prices. Since views are read-only by default, you will create an INSTEAD OF trigger to handle updates on the view.

🎯 Goal: Create a view for book details and prices, then write an INSTEAD OF trigger on the view to update the underlying table when the view is updated.

📋 What You'll Learn

Create a table called books with columns id, title, and price.

Insert three specific books with given ids, titles, and prices.

Create a view called book_prices selecting id, title, and price from books.

Create an INSTEAD OF trigger function called update_book_price that updates the price in books when the view is updated.

Attach the INSTEAD OF trigger to the book_prices view.

💡 Why This Matters

🌍 Real World

Views with INSTEAD OF triggers let you create user-friendly interfaces to complex data, allowing updates through views that normally are read-only.

💼 Career

Database developers and administrators often use INSTEAD OF triggers to maintain data integrity and provide flexible data access in enterprise applications.

1

Create the books table and insert data

Create a table called books with columns id as integer primary key, title as text, and price as numeric(6,2). Insert these rows exactly: (1, 'The Great Gatsby', 10.99), (2, '1984', 8.99), (3, 'To Kill a Mockingbird', 12.50).

PostgreSQL

-- Create the books table and insert the three books
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types. Then use INSERT INTO books with the exact values.

2

Create the book_prices view

Create a view called book_prices that selects id, title, and price from the books table.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC(6,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'To Kill a Mockingbird', 12.50);

-- Create the book_prices view selecting id, title, and price from books
-- Your code here

Need a hint?

Use CREATE VIEW book_prices AS SELECT id, title, price FROM books;

3

Create the INSTEAD OF trigger function

Create a trigger function called update_book_price in PL/pgSQL that updates the price column in the books table when the view is updated. The function should update the row where id = NEW.id and return NEW.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC(6,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'To Kill a Mockingbird', 12.50);

CREATE VIEW book_prices AS
SELECT id, title, price FROM books;

-- Create the trigger function update_book_price
-- Your code here

Need a hint?

Use CREATE FUNCTION with RETURNS trigger. Inside, update books setting price = NEW.price where id = NEW.id, then RETURN NEW.

4

Attach the INSTEAD OF trigger to the view

Create an INSTEAD OF UPDATE trigger on the book_prices view that calls the update_book_price function.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC(6,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'To Kill a Mockingbird', 12.50);

CREATE VIEW book_prices AS
SELECT id, title, price FROM books;

CREATE FUNCTION update_book_price() RETURNS trigger AS $$
BEGIN
    UPDATE books SET price = NEW.price WHERE id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the INSTEAD OF UPDATE trigger on book_prices view
-- Your code here

Need a hint?

Use CREATE TRIGGER with INSTEAD OF UPDATE ON book_prices and call update_book_price().