This page in other versions: 9.0 / 9.1 / 9.2 / 9.3 / 9.4  |  Development versions: devel  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4

Chapter 33. Triggers

This chapter provides general information about writing trigger functions. Trigger functions can be written in most of the available procedural languages, including PL/pgSQL (Chapter 36), PL/Tcl (Chapter 37), PL/Perl (Chapter 38), and PL/Python (Chapter 39). After reading this chapter, you should consult the chapter for your favorite procedural language to find out the language-specific details of writing a trigger in it.

It is also possible to write a trigger function in C, although most people find it easier to use one of the procedural languages. It is not currently possible to write a trigger function in the plain SQL function language.

33.1. Overview of Trigger Behavior

A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.

The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)

Once a suitable trigger function has been created, the trigger is established with CREATE TRIGGER. The same trigger function can be used for multiple triggers.

PostgreSQL offers both per-row triggers and per-statement triggers. With a per-row trigger, the trigger function is invoked once for each row that is affected by the statement that fired the trigger. In contrast, a per-statement trigger is invoked only once when an appropriate statement is executed, regardless of the number of rows affected by that statement. In particular, a statement that affects zero rows will still result in the execution of any applicable per-statement triggers. These two types of triggers are sometimes called row-level triggers and statement-level triggers, respectively.

Triggers are also classified as before triggers and after triggers. Statement-level before triggers naturally fire before the statement starts to do anything, while statement-level after triggers fire at the very end of the statement. Row-level before triggers fire immediately before a particular row is operated on, while row-level after triggers fire at the end of the statement (but before any statement-level after triggers).

Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row triggers can return a table row (a value of type HeapTuple) to the calling executor, if they choose. A row-level trigger fired before an operation has the following choices:

  • It can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that invoked the trigger (the insertion or modification of a particular table row).

  • For row-level INSERT and UPDATE triggers only, the returned row becomes the row that will be inserted or will replace the row being updated. This allows the trigger function to modify the row being inserted or updated.

A row-level before trigger that does not intend to cause either of these behaviors must be careful to return as its result the same row that was passed in (that is, the NEW row for INSERT and UPDATE triggers, the OLD row for DELETE triggers).

The return value is ignored for row-level triggers fired after an operation, and so they may as well return NULL.

If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name. In the case of before triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger. If any before trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired.

Typically, row before triggers are used for checking or modifying the data that will be inserted or updated. For example, a before trigger might be used to insert the current time into a timestamp column, or to check that two elements of the row are consistent. Row after triggers are most sensibly used to propagate the updates to other tables, or make consistency checks against other tables. The reason for this division of labor is that an after trigger can be certain it is seeing the final value of the row, while a before trigger cannot; there might be other before triggers firing after it. If you have no specific reason to make a trigger before or after, the before case is more efficient, since the information about the operation doesn't have to be saved until end of statement.

If a trigger function executes SQL commands then these commands may fire triggers again. This is known as cascading triggers. There is no direct limitation on the number of cascade levels. It is possible for cascades to cause a recursive invocation of the same trigger; for example, an INSERT trigger might execute a command that inserts an additional row into the same table, causing the INSERT trigger to be fired again. It is the trigger programmer's responsibility to avoid infinite recursion in such scenarios.

When a trigger is being defined, arguments can be specified for it. The purpose of including arguments in the trigger definition is to allow different triggers with similar requirements to call the same function. As an example, there could be a generalized trigger function that takes as its arguments two column names and puts the current user in one and the current time stamp in the other. Properly written, this trigger function would be independent of the specific table it is triggering on. So the same function could be used for INSERT events on any table with suitable columns, to automatically track creation of records in a transaction table for example. It could also be used to track last-update events if defined as an UPDATE trigger.

Each programming language that supports triggers has its own method for making the trigger input data available to the trigger function. This input data includes the type of trigger event (e.g., INSERT or UPDATE) as well as any arguments that were listed in CREATE TRIGGER. For a row-level trigger, the input data also includes the NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and DELETE triggers. Statement-level triggers do not currently have any way to examine the individual row(s) modified by the statement.

Comments


Jan. 12, 2006, 1:25 p.m.

Trigger inheritance.

We have table child inherited from table parent.
One could think when inserting|updating into child.col1 that the trigger on parent insert|update will trigger.
That is not the case.
if you want such a behaviour you have to have the trigger on both tables.


July 19, 2006, 9:08 p.m.

"It is also possible to write a trigger function in C, although most people find it easier to use one of the procedural languages."

However, the only immediate example is a trigger based on a C function. Suggest that we add a simple example OR move the example under 36.10 to this chapter. Alternatively, adding a page that links to various examples would be pretty effective.


Aug. 5, 2006, 6:43 a.m.

Consider this example, using PG/psql: You want to monitor price changes, so you create a table to record the barcode, the new price and when it changed:

CREATE TABLE price_change (
apn CHARACTER(15) NOT NULL,
effective TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
price NUMERIC(9,2),
UNIQUE (apn, effective)
);

Next you write a trigger function to insert records as required:

CREATE OR REPLACE FUNCTION insert_price_change() RETURNS trigger AS '
DECLARE
changed boolean;
BEGIN
IF tg_op = ''DELETE'' THEN
INSERT INTO price_change(apn, effective, price)
VALUES (old.barcode, CURRENT_TIMESTAMP, NULL);
RETURN old;
END IF;
IF tg_op = ''INSERT'' THEN
changed := TRUE;
ELSE
changed := new.price IS NULL != old.price IS NULL OR new.price != old.price;
END IF;
IF changed THEN
INSERT INTO price_change(apn, effective, price)
VALUES (new.barcode, CURRENT_TIMESTAMP, new.price);
END IF;
RETURN new;
END
' LANGUAGE plpgsql;

Finally you create a trigger on the table or tables you wish to monitor:

CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON stock
FOR EACH ROW EXECUTE PROCEDURE insert_price_change();


Sept. 12, 2006, 3:42 p.m.

Row-level before triggers fire immediately before a particular row is operated on, while row-level after triggers fire at the end of the statement (after all rows have been operated on but before any statement-level after triggers).

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group