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

PostgreSQL comes with two main types of triggers: row-level trigger and statement-
level trigger . These are speciied with FOR EACH ROW (row-level triggers) and FOR
EACH STATEMENT (statement-level triggers). The two can be differentiated by how
many times the trigger is invoked and at what time. This means that if an UPDATE
statement is executed that affects 10 rows, the row-level trigger will be invoked 10
times, whereas the statement-level trigger deined for a similar operation will be
invoked only once per SQL statement.
Triggers can be attached to both tables and views. Triggers can be ired for tables
before or after any INSERT , UPDATE , or DELETE operation; they can be ired once per
affected row, or once per SQL statement. Triggers can be executed for the TRUNCATE
statements as well. When a trigger event occurs, the trigger function is invoked to
make the appropriate changes as per the logic you have deined in the trigger function.
The triggers deined with INSTEAD OF are used for INSERT , UPDATE , or DELETE on the
views. In the case of views, triggers ired before or after INSERT , UPDATE , or DELETE
can only be deined at the statement level, whereas triggers that ire INSTEAD OF on
INSERT , UPDATE , or DELETE will only be deined at the row level.
Triggers are quite helpful where your database is being accessed by multiple
applications, and you want to maintain complex data integrity (this will be
dificult with available means) and monitor or log changes whenever a table
data is being modiied.
The next topic is a concise explanation of tricky trigger concepts and behaviors
that we discussed previously. They can be helpful in a database design that
involves triggers.
Tricky triggers
In FOR EACH ROW triggers, function variables contain table rows as either a NEW or
OLD record variable, for example, in the case of INSERT , the table rows will be NEW ,
for DELETE , it is OLD , and for UPDATE , it will be both. The NEW variable contains the
row after UPDATE and OLD variable holds the row state before UPDATE .
Hence, you can manipulate this data in contrast to FOR EACH STATEMENT triggers.
This explains one thing clearly, that if you have to manipulate data, use FOR EACH
ROW triggers.
The next question that strikes the mind is how to choose between row-level AFTER
and BEFORE triggers.