SQL Academy · Lesson

Updatable Views and INSTEAD OF Triggers

Make views updatable directly, or use INSTEAD OF triggers when the view aggregates or joins data.

Updatable Views and INSTEAD OF Triggers is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Automatically Updatable Views

PostgreSQL makes simple views updatable automatically. INSERT/UPDATE/DELETE on the view is rewritten as the same operation on the base table:

CREATE VIEW active_users AS
  SELECT id, email, full_name FROM users WHERE deleted_at IS NULL;

UPDATE active_users SET full_name = 'Alice' WHERE id = 1;
-- Translates to UPDATE on the users table.

Requirements for Auto-Updatable

The view must:

Reference exactly ONE base table
Have no aggregates, GROUP BY, HAVING, DISTINCT
Have no LIMIT, OFFSET
Have no set operations (UNION etc.)
Map columns directly (no expressions in the SELECT list — or only some)

WITH CHECK OPTION

Forbid writes that would create rows invisible to the view:

CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL
  WITH CHECK OPTION;

INSERT INTO active_users (email, deleted_at) VALUES (..., NOW());
-- ERROR — would create a row outside the view's WHERE.

Complex Views Need INSTEAD OF

Views with joins, aggregates, or expressions aren't auto-updatable. You must write an INSTEAD OF trigger that defines what writing means:

CREATE VIEW user_with_address AS
  SELECT u.id, u.email, a.city, a.country
  FROM users u JOIN addresses a ON a.user_id = u.id;

CREATE TRIGGER user_addr_update
INSTEAD OF UPDATE ON user_with_address
FOR EACH ROW EXECUTE FUNCTION update_user_addr();

INSTEAD OF Trigger Function

The function decides which base tables to write:

CREATE FUNCTION update_user_addr() RETURNS TRIGGER AS $$
BEGIN
  UPDATE users SET email = NEW.email WHERE id = NEW.id;
  UPDATE addresses SET city = NEW.city, country = NEW.country WHERE user_id = NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

When INSTEAD OF Beats App Logic

If many consumers update the view, centralising the update logic in a trigger gives consistency. If only one app updates, keep the logic in app code.

SECURITY BARRIER

Prevent the planner from leaking predicates into untrusted view consumers (information leak via cost):

CREATE VIEW my_things AS
SELECT * FROM things WHERE owner_id = current_user_id()
WITH (security_barrier);

Row-Level Security as an Alternative

Modern PostgreSQL also supports Row-Level Security (RLS) on base tables — often cleaner than building security views.

ALTER TABLE things ENABLE ROW LEVEL SECURITY;
CREATE POLICY owner_only ON things USING (owner_id = current_user_id());

Recursive Views

PostgreSQL supports recursive views (a hierarchy you can SELECT from):

CREATE RECURSIVE VIEW tree (id, parent_id, depth) AS
  SELECT id, parent_id, 0 FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent_id = t.id;

Permissions

Grant on the view, not the base tables:

GRANT SELECT, UPDATE ON active_users TO app_user;

Performance Note

An INSTEAD OF trigger fires per row. For bulk DML, that's slow. Often you're better off having callers write to the base tables directly.

Recap

Views can be writable.

Simple views: auto-updatable
Complex views: INSTEAD OF triggers
WITH CHECK OPTION enforces visibility
RLS is often a cleaner security model

Quick Check

You define a view that joins two tables. Can you UPDATE through it by default?

Frequently asked questions

Is the “Updatable Views and INSTEAD OF Triggers” lesson free?

Yes — the full text of “Updatable Views and INSTEAD OF Triggers” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Updatable Views and INSTEAD OF Triggers”?

Make views updatable directly, or use INSTEAD OF triggers when the view aggregates or joins data. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Updatable Views and INSTEAD OF Triggers” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Plain Views: Logical Reuse
Updatable Views and INSTEAD OF Triggers
Materialized Views and REFRESH Strategies
When to Pre-Aggregate

← Back to SQL Academy