PostgreSQL Triggers: BEFORE/AFTER, Row and Statement Level, Transition Tables, updated_at and Audit Triggers, Event Triggers

Version note: The examples in this post were run on PostgreSQL 18.7 (the official postgres:18 Docker image) right after loading the Bookstore schema; the .NET example was built and run with .NET 10, Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 and EF Core 10.

Guess First

For Bookstore, the online bookstore used as the shared example across this site's series, we put four triggers on the books table: before and after UPDATE, at statement and row level. All of them call the same function, which only prints when, at which level and for which row it runs:

CREATE FUNCTION trace_trigger() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE '% % %: %', TG_WHEN, TG_LEVEL, TG_OP, CASE WHEN TG_LEVEL = 'ROW' THEN NEW.book_id::text ELSE '-' END;
    IF TG_LEVEL = 'ROW' THEN
        RETURN NEW;
    END IF;
    RETURN NULL;
END
$$;
CREATE TRIGGER t1 BEFORE UPDATE ON books FOR EACH STATEMENT EXECUTE FUNCTION trace_trigger();
CREATE TRIGGER t2 BEFORE UPDATE ON books FOR EACH ROW EXECUTE FUNCTION trace_trigger();
CREATE TRIGGER t3 AFTER UPDATE ON books FOR EACH ROW EXECUTE FUNCTION trace_trigger();
CREATE TRIGGER t4 AFTER UPDATE ON books FOR EACH STATEMENT EXECUTE FUNCTION trace_trigger();
UPDATE books SET price = price WHERE book_id IN (1, 2);
UPDATE books SET price = price WHERE book_id = -1;

How many messages does the first UPDATE, which updates two rows, print, and in what order? Does the second UPDATE, which finds no rows at all, run any trigger?

NOTICE:  BEFORE STATEMENT UPDATE: -
NOTICE:  BEFORE ROW UPDATE: 1
NOTICE:  BEFORE ROW UPDATE: 2
NOTICE:  AFTER ROW UPDATE: 1
NOTICE:  AFTER ROW UPDATE: 2
NOTICE:  AFTER STATEMENT UPDATE: -
UPDATE 2

NOTICE:  BEFORE STATEMENT UPDATE: -
NOTICE:  AFTER STATEMENT UPDATE: -
UPDATE 0

Two details surprise most people. AFTER ROW triggers do not run right after each row but one after another once the statement has processed all rows: PostgreSQL collects them in a queue. Statement-level triggers do not look at the row count; they ran once each even for an UPDATE that affected zero rows. SET price = price, which changes no value, also fired the triggers; PostgreSQL does not skip a row because "nothing changed". One more difference for readers coming from SQL Server: SQL Server's DML triggers run only at statement level and always AFTER (or INSTEAD OF), reading rows from the inserted/deleted tables; PostgreSQL also has row level, BEFORE, and the ability to change the row. In this post we look at these rules of triggers, updated_at and audit triggers, their cost on bulk updates, INSTEAD OF and event triggers, and how they interact with EF Core.

The Trigger Function: NEW, OLD, TG_OP and the Return Value

A trigger in PostgreSQL has two parts: a parameterless function of type RETURNS trigger, and a CREATE TRIGGER that binds it to a table, an event and a timing. The same function can be attached to several tables; if a table-specific argument is needed, it is given in CREATE TRIGGER and read from TG_ARGV inside the function. Inside the function PL/pgSQL provides special variables: NEW (the row to be written, in INSERT and UPDATE), OLD (the old row, in UPDATE and DELETE), TG_OP (INSERT, UPDATE, DELETE, TRUNCATE), TG_WHEN, TG_LEVEL, TG_TABLE_NAME and TG_TABLE_SCHEMA. At statement level there is no NEW or OLD; that is why the CASE in "Guess First" is there.

The return value matters only in a BEFORE ROW trigger: if you return NEW (or a modified copy), the row is written in that form; if you return NULL, the operation is silently skipped for that row and the following triggers do not run either. In DELETE, NEW is empty, so OLD is returned. In AFTER and statement triggers the return value is ignored; writing RETURN NULL is the convention. If a table has several triggers for the same event, they run in alphabetical order of their names; if the order matters, name them accordingly (trg_10_..., trg_20_...).

The rule is simple: changing the row itself (filling a column, normalizing, rejecting) is a job for BEFORE ROW; writing to other tables or sending a notification (like the pg_notify trigger in post 11) is a job for AFTER, because when AFTER runs the row has passed the constraints and has its final form.

updated_at: A BEFORE ROW Trigger and a WHEN Condition

Bookstore's books table has no modification time. Let us add the column and fill it with a trigger on every update:

ALTER TABLE books ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();
CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END
$$;
CREATE TRIGGER trg_books_updated_at
    BEFORE UPDATE ON books
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE FUNCTION set_updated_at();
SELECT book_id, price, updated_at FROM books WHERE book_id IN (1, 2) ORDER BY book_id;
SELECT pg_sleep(1);
UPDATE books SET price = price + 1 WHERE book_id = 1;
UPDATE books SET price = price WHERE book_id = 2;
SELECT book_id, price, updated_at FROM books WHERE book_id IN (1, 2) ORDER BY book_id;
UPDATE books SET updated_at = '2020-01-01' WHERE book_id = 2;
SELECT book_id, price, updated_at FROM books WHERE book_id = 2;

ALTER TABLE
Time: 0.809 ms

 book_id | price |          updated_at
---------+-------+-------------------------------
       1 | 32.77 | 2026-11-15 00:50:27.986065+00
       2 | 39.00 | 2026-11-15 00:50:27.986065+00

 book_id | price |          updated_at
---------+-------+-------------------------------
       1 | 33.77 | 2026-11-15 00:50:28.994224+00
       2 | 39.00 | 2026-11-15 00:50:27.986065+00

 book_id | price |          updated_at
---------+-------+-------------------------------
       2 | 39.00 | 2026-11-15 00:50:28.998739+00

The NOT NULL DEFAULT now() column was added to the 40,000-row table in 0.8 ms. Since PostgreSQL 11, a column added with a non-volatile default does not rewrite the table; the value is computed once and kept in the catalog, and all existing rows see it. That is why both books have the same time in the first query. With DEFAULT clock_timestamp() the table would have been rewritten.

The time of book 1, whose price changed, was updated. That of book 2, whose value stayed the same, did not change: WHEN (OLD.* IS DISTINCT FROM NEW.*) did not call the function at all. A WHEN condition is cheaper than an IF inside the trigger function, because the function call and the switch into PL/pgSQL never happen. PostgreSQL also has a built-in function for this purpose: suppress_redundant_updates_trigger() skips an update that changes no value at all. In the last UPDATE the application tried to set updated_at back to 2020 by hand; because the row changed, the trigger ran and wrote the current time again. No code in the application can break the meaning of the column.

An AFTER Trigger That Updates Its Own Table

Trying to do the same job with an UPDATE in an AFTER trigger is a common mistake:

CREATE FUNCTION touch_book_after() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE books SET updated_at = now() WHERE book_id = NEW.book_id;
    RETURN NULL;
END
$$;
CREATE TRIGGER trg_books_touch AFTER UPDATE ON books FOR EACH ROW EXECUTE FUNCTION touch_book_after();
UPDATE books SET price = price + 1 WHERE book_id = 3;

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."authors" x WHERE "author_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
SQL statement "UPDATE books SET updated_at = now() WHERE book_id = NEW.book_id"
PL/pgSQL function touch_book_after() line 3 at SQL statement
SQL statement "UPDATE books SET updated_at = now() WHERE book_id = NEW.book_id"
PL/pgSQL function touch_book_after() line 3 at SQL statement
...

The UPDATE done by the trigger fired the trigger again, which did another UPDATE. The same two lines repeated 663 times in the error message, and when the stack was full the statement was rolled back (we shortened the output). The innermost line is interesting: foreign key checks are also done with PostgreSQL's own internal triggers. Because this error is fast and loud, it does not reach production in this form; the dangerous cases are chains in which two tables update each other and loop only for certain data. pg_trigger_depth() tells you how deeply nested the current trigger is and can be used to break a loop, but the right fix is to change the row through NEW in a BEFORE trigger.

An Audit Trigger: Storing Changed Columns as jsonb

When the status of an order changes, we want to record who changed what and when. One audit table for all tables, and a trigger that writes the changed columns with their old and new values as jsonb:

CREATE TABLE audit_log
(
    audit_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name text        NOT NULL,
    operation  text        NOT NULL,
    row_id     int         NOT NULL,
    changes    jsonb       NOT NULL,
    changed_by text        NOT NULL DEFAULT session_user,
    changed_at timestamptz NOT NULL DEFAULT now()
);
CREATE FUNCTION audit_order_change() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, row_id, changes)
    SELECT TG_TABLE_NAME, TG_OP, NEW.order_id,
           jsonb_object_agg(n.key, jsonb_build_object('old', to_jsonb(OLD) -> n.key, 'new', n.value))
    FROM jsonb_each(to_jsonb(NEW)) AS n
    WHERE to_jsonb(OLD) -> n.key IS DISTINCT FROM n.value;
    RETURN NULL;
END
$$;
CREATE TRIGGER trg_orders_audit
    AFTER UPDATE ON orders
    FOR EACH ROW
    WHEN (OLD.status IS DISTINCT FROM NEW.status OR OLD.total_amount IS DISTINCT FROM NEW.total_amount)
    EXECUTE FUNCTION audit_order_change();
UPDATE orders SET status = 'Shipped' WHERE order_id = 1000000;
UPDATE orders SET status = 'Shipped' WHERE order_id = 1000000;
UPDATE orders SET status = 'Delivered', total_amount = total_amount - 5 WHERE order_id = 1000000;
SELECT audit_id, row_id, changes, changed_by FROM audit_log ORDER BY audit_id;

 audit_id | row_id  |                                             changes                                              |   changed_by
----------+---------+--------------------------------------------------------------------------------------------------+-----------------
        1 | 1000000 | {"status": {"new": "Shipped", "old": "Pending"}}                                                 | bookstore_owner
        2 | 1000000 | {"status": {"new": "Delivered", "old": "Shipped"}, "total_amount": {"new": 44.34, "old": 49.34}} | bookstore_owner

to_jsonb(NEW) turns the row into an object of column names and values, jsonb_each expands it into key/value rows (post 05), and jsonb_object_agg collects only the differing ones again (post 20). Because the function knows no column names apart from NEW.order_id, it can be attached to other tables as well. The second UPDATE wrote the same value to an order that was already Shipped, so it did not pass the WHEN condition and produced no audit row. The record is written in the same transaction as the main change: if the order update is rolled back, the audit record is rolled back too, which is what you expect from an audit.

As changed_by shows, the trigger only knows the database role. If the application connects all users through a single role, you need to carry the real user into the trigger through a session setting (SET LOCAL app.user_id = ...), like app.customer_id in post 14. The design side of an audit trail, meaning what is kept, for how long and in which form, is in the audit trail post of the Çözüm Desenleri series.

Cost on a Bulk UPDATE: Row Level and Transition Tables

A row-level trigger means one function call and one INSERT per row. Imagine changing the amount of 200,000 orders with a single UPDATE because of a campaign correction. A statement-level trigger can receive the old and new versions of the affected rows as tables (transition tables) through REFERENCING and write the audit with a single INSERT ... SELECT:

CREATE FUNCTION audit_order_amount_row() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, row_id, changes)
    VALUES (TG_TABLE_NAME, TG_OP, NEW.order_id,
            jsonb_build_object('total_amount', jsonb_build_object('old', OLD.total_amount, 'new', NEW.total_amount)));
    RETURN NULL;
END
$$;
CREATE FUNCTION audit_order_amount_bulk() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, row_id, changes)
    SELECT TG_TABLE_NAME, TG_OP, n.order_id,
           jsonb_build_object('total_amount', jsonb_build_object('old', o.total_amount, 'new', n.total_amount))
    FROM new_rows AS n
    JOIN old_rows AS o ON o.order_id = n.order_id
    WHERE o.total_amount IS DISTINCT FROM n.total_amount;
    RETURN NULL;
END
$$;
CREATE TRIGGER trg_orders_amount_row
    AFTER UPDATE OF total_amount ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_order_amount_row();
CREATE TRIGGER trg_orders_amount_bulk
    AFTER UPDATE ON orders
    REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
    FOR EACH STATEMENT
    EXECUTE FUNCTION audit_order_amount_bulk();
UPDATE orders SET total_amount = total_amount + 1 WHERE order_id <= 200000;

In each attempt only one trigger was enabled and the UPDATE ran inside a transaction that was rolled back; we ran the four variants for three rounds (db/23-trigger-cost.sql, times in ms):

The result is more nuanced than the first guess. A simple row trigger that writes one column added 20–45% to the 200,000-row update, depending on the round; the statement trigger with transition tables added 0–35%. There is a difference, but not a large one; what the trigger does per row determines the real cost. The generic audit function above converts each row to jsonb twice and compares all its columns, so it made the update 2.3 to 3.4 times slower. The measurements are noisy (even the update without a trigger varied between 1.5 and 2.4 seconds); measure with your own data before deciding. Transition tables have two limits: they cannot be combined with a column list such as UPDATE OF total_amount (trying it gave transition tables cannot be specified for triggers with column lists, which is why the bulk trigger has no column list), and they do not exist for INSTEAD OF triggers.

INSTEAD OF: Making a Join View Writable

In post 21, when we tried to update the GROUP BY view v_category_stats, PostgreSQL suggested an INSTEAD OF trigger. The same applies to views that join two tables. The warehouse screen uses a view that shows price and stock quantity in one row:

CREATE VIEW v_book_stock AS
SELECT b.book_id, b.title, b.price, s.quantity_on_hand
FROM books AS b
JOIN stock_items AS s ON s.book_id = b.book_id;
UPDATE v_book_stock SET price = 19.90, quantity_on_hand = 50 WHERE book_id = 5;
CREATE FUNCTION v_book_stock_update() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.book_id IS DISTINCT FROM OLD.book_id THEN
        RAISE EXCEPTION 'book_id cannot be changed through v_book_stock';
    END IF;
    UPDATE books SET title = NEW.title, price = NEW.price
    WHERE book_id = OLD.book_id
      AND (title, price) IS DISTINCT FROM (NEW.title, NEW.price);
    UPDATE stock_items SET quantity_on_hand = NEW.quantity_on_hand, updated_at = now()
    WHERE book_id = OLD.book_id
      AND quantity_on_hand IS DISTINCT FROM NEW.quantity_on_hand;
    RETURN NEW;
END
$$;
CREATE TRIGGER trg_v_book_stock_update
    INSTEAD OF UPDATE ON v_book_stock
    FOR EACH ROW
    EXECUTE FUNCTION v_book_stock_update();
UPDATE v_book_stock SET price = 19.90, quantity_on_hand = 50 WHERE book_id = 5 RETURNING *;
DELETE FROM v_book_stock WHERE book_id = 5;
SELECT b.price, s.quantity_on_hand FROM books AS b JOIN stock_items AS s USING (book_id) WHERE book_id = 5;

ERROR:  cannot update view "v_book_stock"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

 book_id |     title     | price | quantity_on_hand
---------+---------------+-------+------------------
       5 | Foundation #5 | 19.90 |               50
UPDATE 1

ERROR:  cannot delete from view "v_book_stock"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.

 price | quantity_on_hand
-------+------------------
 19.90 |               50

INSTEAD OF triggers exist only on views and only at row level: for each row that comes to the view, PostgreSQL calls the function instead of doing the work itself, and reflects the NEW the function returns in RETURNING and in the UPDATE 1 count. Had it returned NULL, the row would count as "not processed". The function decides itself what goes to which table; we put an IS DISTINCT FROM condition on both UPDATEs so that an unchanged table is not touched, and a price-only change does not write an empty row version into stock_items. Because the trigger is defined only for UPDATE, DELETE is still rejected; each operation you want to support needs its own trigger. For aggregate views like v_category_stats, a request such as "set the number of books in the category to 0" has no meaningful translation, so the right choice is to leave the view read-only instead of writing an INSTEAD OF trigger.

Recording DDL with an Event Trigger

Ordinary triggers run on row changes; event triggers run on DDL commands across the database. An example that records who touched the schema in production and when. CREATE EVENT TRIGGER needs superuser privileges, so the setup is done as postgres:

CREATE TABLE public.ddl_log
(
    ddl_log_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    command_tag     text        NOT NULL,
    object_type     text        NOT NULL,
    object_identity text,
    executed_by     text        NOT NULL,
    executed_at     timestamptz NOT NULL DEFAULT now()
);
CREATE FUNCTION public.log_ddl() RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
    INSERT INTO ddl_log (command_tag, object_type, object_identity, executed_by)
    SELECT c.command_tag, c.object_type, c.object_identity, session_user
    FROM pg_event_trigger_ddl_commands() AS c;
END
$$;
CREATE EVENT TRIGGER trg_log_ddl ON ddl_command_end EXECUTE FUNCTION public.log_ddl();

Then we ran four DDL commands in a separate session as bookstore_owner and read the log as postgres:

ALTER TABLE books ADD COLUMN subtitle text;
CREATE INDEX ix_books_updated_at ON books (updated_at);
DROP INDEX ix_books_updated_at;
ALTER TABLE books DROP COLUMN subtitle;
SELECT ddl_log_id, command_tag, object_type, object_identity, executed_by FROM ddl_log ORDER BY ddl_log_id;

 ddl_log_id | command_tag  | object_type |      object_identity       |   executed_by
------------+--------------+-------------+----------------------------+-----------------
          1 | ALTER TABLE  | table       | public.books               | bookstore_owner
          2 | CREATE INDEX | index       | public.ix_books_updated_at | bookstore_owner
          3 | ALTER TABLE  | table       | public.books               | bookstore_owner

Three of the four commands were recorded; DROP INDEX is missing. On the ddl_command_end event, pg_event_trigger_ddl_commands() does not return dropped objects; for those you need a separate sql_drop event trigger and pg_event_trigger_dropped_objects(). The other events are ddl_command_start (before the command runs, for example to reject DDL at certain hours), table_rewrite, and login, added in PostgreSQL 17. We made the function SECURITY DEFINER so that the role running the DDL does not need write access to ddl_log; the search_path rule from post 22 applies here too. If an event trigger fails, the DDL command fails as well; a badly written event trigger can stop every migration. Since PostgreSQL 17 a superuser can turn them off temporarily with SET event_triggers = off and fix the problem.

EF Core and Tables with Triggers

When a trigger changes updated_at, EF Core does not know about it. With the Npgsql provider from post 12 we mapped books with two configurations; the only difference is that the UpdatedAt property is marked with ValueGeneratedOnAddOrUpdate():

PropertyBuilder<DateTime> updatedAt = book.Property(b => b.UpdatedAt).HasColumnName("updated_at");
if (ReadTriggerValues)
{
    updatedAt.ValueGeneratedOnAddOrUpdate();
}

Book book = await db.Books.SingleAsync(b => b.BookId == 10);
DateTime before = book.UpdatedAt;
book.Price += 1;
await db.SaveChangesAsync();

DateTime inDatabase = await db.Books.Where(b => b.BookId == 10).Select(b => b.UpdatedAt).SingleAsync();
Console.WriteLine($"entity: {book.UpdatedAt:HH:mm:ss.fff} (before {before:HH:mm:ss.fff}), database: {inDatabase:HH:mm:ss.fff}");

-- naive
UPDATE books SET price = @p0
WHERE book_id = @p1;
entity: 00:50:27.986 (before 00:50:27.986), database: 00:53:35.252

-- trigger-aware
UPDATE books SET price = @p0
WHERE book_id = @p1
RETURNING updated_at;
entity: 00:53:35.307 (before 00:53:35.252), database: 00:53:35.307

We removed EF Core's log lines from the output and kept only the UPDATE commands. In the first configuration SaveChanges succeeded but UpdatedAt on the entity kept the old value; code that keeps using this entity in the same context (an API response, a cache entry) sees the wrong time. In the second, EF Core added RETURNING updated_at to the UPDATE and read back the value written by the trigger in the same round trip. The same approach works for any column a trigger fills; if it is filled on insert, use ValueGeneratedOnAdd(). A note for readers coming from SQL Server: since EF Core 7, tables with triggers on SQL Server must be declared with HasTrigger(...), because the OUTPUT clause EF uses does not work together with triggers. PostgreSQL's RETURNING has no such limit; no extra setting was needed with Npgsql. ExecuteUpdate and ExecuteDelete do not use the change tracker at all, so they never carry the trigger's effect into an entity anyway.

In Production

Triggers are invisible: a developer looking at a table sees them only when searching for them. In psql, \d books lists the triggers below the table; the pg_trigger catalog (filtering out the internal foreign key triggers with NOT tgisinternal) and pg_get_triggerdef give you all of them. Mention every trigger in the migration, with a name that says what it does, and in the table's documentation.

There are two ways to turn triggers off during bulk loads and maintenance. ALTER TABLE ... DISABLE TRIGGER trg_orders_audit disables a single trigger, but it takes a strong lock on the table, even if briefly, and affects all sessions. SET session_replication_role = replica skips all ordinary triggers and foreign key checks only in that session; it requires a superuser and leaves data consistency to you. The same setting is at work in logical replication: on the subscriber, triggers do not run by default, and are turned on with ENABLE REPLICA TRIGGER or ENABLE ALWAYS TRIGGER if needed.

When a trigger, when application code? Small rules about the data itself that must hold on every write path (the application, migrations, manual fixes) suit triggers: updated_at, audit, keeping a derived column current. If the derived column is computed only from values in the same row, the generated column from posts 04 and 16 is simpler. Work that goes out to the world, such as sending an e-mail or notifying another service, should not go into a trigger: the e-mail is sent even if the transaction is rolled back. Instead the trigger (or the application) writes to an outbox table in the same transaction and a separate worker sends it; that is why the outbox post of the Message Queue'larla Çalışmak series and the SKIP LOCKED relay in post 09 exist.

Common Pitfalls

Trigger chains and infinite loops: an AFTER trigger that updates its own table, or two tables that update each other. If you change the row, use BEFORE and NEW; if you write to another table, cut unnecessary firing with a WHEN condition.

Not measuring the cost of a row-level trigger on a bulk UPDATE: a simple trigger is acceptable, a generic trigger that processes jsonb per row can make the job three times slower. Test nightly bulk jobs with the triggers enabled.

A business rule hidden in a trigger that nobody knows about: the "I changed the price, why did the stock table change too?" question in the application. Keep triggers few, small and documented; do not build a workflow out of triggers.

Not telling EF Core about a column a trigger fills: after SaveChanges the entity carries the old value. Turn on RETURNING with ValueGeneratedOnAddOrUpdate.

A Small Experiment

★☆☆ Remove the WHEN condition of trg_books_updated_at and add a second BEFORE trigger that calls suppress_redundant_updates_trigger(). Which row count does psql show for an update that changes nothing (UPDATE 0 or UPDATE 1)? Why does the alphabetical order of the two triggers matter?

★★☆ Extend audit_order_change so that it also works for DELETE: record OLD depending on TG_OP. Then run a chunked delete like archive_shipments from post 22 with this trigger enabled and watch the size of the audit table with pg_total_relation_size.

★★★ Write an INSTEAD OF INSERT for v_book_stock: the new book goes into books, the stock row into stock_items, and RETURNING book_id returns the newly generated number. In EF Core, map this view with ToTable instead of ToView and try an Add + SaveChanges; look at the generated SQL and the result.

In the next post we move on to the last topic of this extension, everyday work with psql: the \d family, \x and \timing, the difference between \copy and COPY, \watch, \gexec, .psqlrc, schemas and search_path.