Triggers, Rules, and Views - PostgreSQL Developer's Guide - page 80

Well, if you want to modify new values of INSERT / UPDATE before being stored to
the table, you should use the BEFORE trigger. If your logic requires that for a certain
operation on the current table, it should modify other tables as well, it's time to
choose AFTER triggers. The row-level AFTER triggers are certain about the inal state
whereas the row-level BEFORE trigger cannot view the inal state. One explanation
for this can be that you might end up changing the other tables but the original table
does not allow you to reach the inal desired state.
If the row-level BEFORE triggers and if you return a non-null value, it should match
the structure of the table to be modiied but if you return null, it will skip rest of the
operations and any INSERT / UPDATE / DELETE operation will not be effective.
The return value of a statement-level BEFORE trigger and an AFTER trigger is ignored
by PostgreSQL. So, you can return null from a row-level AFTER trigger.
If we have to categorize it with respect to tables and views, the following facts
should be kept in mind:
• The statement-level BEFORE and AFTER triggers can be deined on tables
and views
• The row-level BEFORE and AFTER triggers should only be deined on tables
• The row-level INSTEAD OF triggers should only be deined on views
Creating triggers and trigger functions with
PL/pgSQL
It is time to see triggers in action. For the sake of understanding, we will write trigger
functions in PL/pgSQL irst and then advance our skills by writing in other native
languages as well.
Let's stick to the warehouse_db database and the warehouse_tbl table that reside
inside the record schema.
The table you want to modify should exist in the same database
as the table or view to which the trigger is attached.