9.3 Writing Triggers

Almost all sophisticated databases support triggers and so does PostgreSQL. Triggers are a method to execute functions automatically whenever a certain event happens. For instance, you can define a trigger that deletes a record from table A every time a record is inserted into table B. In this scenario, you would have to define a trigger on table B, which is waiting for records to be inserted.

A trigger can start almost all kinds of functions, but in many cases PL/pgSQL is used to implement triggers because it has been optimized for being integrated into the database. If you don't like writing PL/pgSQL functions, you can also use PL/Tcl or PL/Perl. However, in this section we will focus entirely on writing triggers with the help of PL/pgSQL and pure SQL.

9.3.1 Creating and Dropping Triggers

The most important command when working with triggers is the CREATE TRIGGER command. Let's take a look at the syntax overview of the CREATE TRIGGER command:

 phpbook=# \h CREATE TRIGGER Command:     CREATE TRIGGER Description: Creates a new trigger Syntax: CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }     ON table FOR EACH { ROW | STATEMENT }     EXECUTE PROCEDURE func ( arguments )

As you can see, triggers can be defined to execute a function before or after a certain event. If a trigger is executed before a certain event, it is possible to modify the parameters of the command the trigger was fired for you will have a closer look at that in this section.

A trigger can either be executed for a certain row or a certain statement. If you are working with COPY commands affecting more than just one line of data, it can make a significant difference whether FOR EACH ROW or FOR EACH STATEMENT is used. In the case of FOR EACH ROW, the trigger will be executed for every row changed in the data. If a lot of data is inserted into the database, this might have a significant impact on performance because at least one function is called for every line processed by PL/pgSQL.

In version 7.2, FOR EACH STATEMENT has not been implemented yet.

With the help of CREATE TRIGGER, it is possible to tell PostgreSQL to pass a list of parameters to the function called by the trigger.

To remove a trigger from the database again, the DROP TRIGGER has to be called:

 phpbook=# \h DROP TRIGGER Command:     DROP TRIGGER Description: Removes the definition of a trigger Syntax: DROP TRIGGER name ON table

9.3.2 `pg_trigger`

To understand what CREATE TRIGGER and DROP TRIGGER do internally, it is necessary to have a closer look at the system table called pg_trigger:

 phpbook=# \d pg_trigger            Table "pg_trigger"    Attribute    |    Type    | Modifier ----------------+------------+----------  tgrelid        | oid        |  tgname         | name       |  tgfoid         | oid        |  tgtype         | smallint   |  tgenabled      | boolean    |  tgisconstraint | boolean    |  tgconstrname   | name       |  tgconstrrelid  | oid        |  tgdeferrable   | boolean    |  tginitdeferred | boolean    |  tgnargs        | smallint   |  tgattr         | int2vector |  tgargs         | bytea      | Indices: pg_trigger_oid_index,          pg_trigger_tgconstrname_index,          pg_trigger_tgconstrrelid_index,          pg_trigger_tgrelid_index

A trigger is nothing more than an entry in PostgreSQL's system table for storing information about triggers. In this system table the identification number of the function the trigger is calling, the name of the trigger, the event it is fired for, the arguments passed to the trigger, and many more attributes defining the trigger are stored. If you want to modify a trigger, you just have to update the content of pg_trigger and the behavior of your trigger will change immediately.

9.3.3 Simple Triggers

After this theoretical overview, it is time to take a look at some real-world examples, so that you can see how triggers can be written and how triggers work.

The goal of the first example is to create a trigger that logs every INSERT operation on a table called data. To create this table, you can use a simple SQL command:

 phpbook=# CREATE TABLE data(somedata text); CREATE

The function for reporting every INSERT operation into data is called logfunc. It does nothing other than display a warning and perform and insert a record into table logtable:

 CREATE OR REPLACE FUNCTION logfunc() RETURNS opaque AS '         BEGIN                 RAISE NOTICE ''trigger logfunc has been fired'';                 INSERT INTO logtable(tstamp, message, mestype)                         VALUES(now(), ''data has been inserted'', 0);                 RETURN NEW;         END; ' LANGUAGE 'plpgsql';

As you can see, the function returns opaque. This data type can only be used in combination with triggers and can be compared with void in C and C++.

In this example the function does not contain a DECLARE block because no variables are needed by the main function. At the end of the function, NEW is returned. Take a look at the code of the trigger itself:

 CREATE TRIGGER trig_data AFTER INSERT         ON data FOR EACH ROW         EXECUTE PROCEDURE logfunc();

The trigger is fired after every INSERT operation on data. For every line added to the table, logfunc is executed exactly once. Before executing the function, take a look at the content of logtable:

 phpbook=# SELECT * FROM logtable;  id |            tstamp             |     message      | mestype ----+-------------------------------+------------------+---------   1 | 2001-11-12 19:20:41.270717+01 | an error message |       3   2 | 2001-11-12 19:20:41.270717+01 | the second error |       3 (2 rows)fs

Two records are in the table before performing an INSERT operation:

 phpbook=# INSERT INTO data VALUES ('a piece of text'); NOTICE:  trigger logfunc has been fired INSERT 16642 1

When inserting the data, the message printed by the trigger can be seen on the screen. In addition, one record has been added to data.

Let's query logtable again:

 phpbook=# SELECT * FROM logtable;  id |            tstamp             |        message         | mestype ----+-------------------------------+------------------------+---------   1 | 2001-11-12 19:20:41.270717+01 | an error message       |       3   2 | 2001-11-12 19:20:41.270717+01 | the second error       |       3   3 | 2001-11-13 08:08:12.244384+01 | data has been inserted |       0 (3 rows)

One record has been added to the table by the trigger.

Now it is time to write a slightly more sophisticated trigger that handles INSERT, UPDATE, and DELETE statements by using just one function.

9.3.4 Predefined Variables

When a function is called by a trigger, some variables you can access in the function are already predefined and already contain useful values you might need inside your trigger. In this section you will get an overview of these variables and what you can do with them.

Let's take a look at the list of predefined variables:

NEW In triggers fired for one row of data, NEW will contain the new value of the record the trigger has been started for. The data type of NEW is RECORD. NEW can only be used for triggers fired for INSERT and UPDATE operations.
OLD In triggers fired for one row of data, OLD will contain the value of the record before executing the trigger. The data type of OLD is RECORD and the variable is defined for triggers fired for UPDATE and DELETE statements.
TG_NAME TG_NAME contains the name of the trigger that is currently fired. The data type of TG_NAME is name.
TG_WHEN TG_WHEN contains information about when the trigger has been fired. TG_WHEN can either be AFTER or BEFORE. The data type of the variable is text.
TG_LEVEL To find out about the definition of the trigger, TG_LEVEL can be used. The variable can contain either ROW or STATEMENT depending on the trigger's definition. In PostgreSQL 7.2 or below, the content is always ROW because triggers defined on statements are not yet implemented. The data type of TG_LEVEL is text.
TG_OP Every trigger is fired for a specified event. TG_OP contains either INSERT, UPDATE, or DELETE depending on the event the trigger has been fired for. The data type is text.
TG_RELID All objects in PostgreSQL, such as tables, have an object id that is used to identify an object in the database. The object id of a table a trigger is fired for can be found in TG_RELID. The data type is oid.
TG_RELNAME TG_RELNAME contains the name of a table the trigger is fired for. The data type is name.
TG_NARGS As you have seen in this section, a number of parameters can be passed to a trigger. TG_NARGS contains the number of arguments passed to the function called by the current trigger. The data type is integer.
TG_ARGV[] The parameters passed to a function are stored in an array of text variables. The indexes run from 0 to TG_NARGS minus one.

If you are not using PL/pgSQL but a different embedded language, some predefined variables will also be available. PL/Tcl is a particularly comfortable language for implementing functions used for building triggers. This section does not cover PL/Tcl because it is beyond the scope of this book. If you want to find out more about PL/Tcl and other embedded languages, we recommend reading the PostgreSQL Developer's Handbook from Sams Publishing.

Before you learn about the scope of predefined variables, you will take a look at a simple trigger that displays a set of variables on the screen using RAISE NOTICE. Recall the content of data:

 phpbook=# SELECT * FROM data;     somedata -----------------  a piece of text (1 row)

The table contains exactly one record. The function updatemessage will be used to display a list of all predefined variables:

 CREATE OR REPLACE FUNCTION updatemessage () RETURNS opaque AS '         BEGIN                 RAISE NOTICE ''NEW: %'', NEW.somedata;                 RAISE NOTICE ''OLD: %'', OLD.somedata;                 RAISE NOTICE ''TG_WHEN: %'', TG_WHEN;                 RAISE NOTICE ''TG_LEVEL: %'', TG_LEVEL;                 RAISE NOTICE ''TG_OP: %'', TG_OP;                 RAISE NOTICE ''TG_RELID: %'', TG_RELID;                 RAISE NOTICE ''TG_RELNAME: %'', TG_RELNAME;                 RAISE NOTICE ''TG_NARGS: %'', TG_NARGS;                 RAISE NOTICE ''TG_ARGV: %'', TG_ARGV[0];                 RETURN NEW;         END; ' LANGUAGE 'plpgsql';

To call the function in case of an UPDATE operation, you have to define a trigger:

 CREATE TRIGGER trig_updatemessage AFTER UPDATE         ON data FOR EACH ROW         EXECUTE PROCEDURE updatemessage();

If no error has occurred while inserting the function and the trigger into the database, the trigger will now cause updatemessage to be executed in case of UPDATE:

 phpbook=# UPDATE data SET somedata='Hello World'; NOTICE:  NEW: Hello World NOTICE:  OLD: Hello World NOTICE:  TG_WHEN: AFTER NOTICE:  TG_LEVEL: ROW NOTICE:  TG_OP: UPDATE NOTICE:  TG_RELID: 16647 NOTICE:  TG_RELNAME: data NOTICE:  TG_NARGS: 0 NOTICE:  TG_ARGV: <OUT_OF_RANGE> UPDATE 1

Every value but TG_ARGV[0] contains a useful value. TG_ARGV is empty because no parameters have been passed to the function. Therefore index number zero is out of range.

Let's see if the UPDATE operation has been executed successfully:

 phpbook=# SELECT * FROM data;   somedata -------------  Hello World (1 row)

The trigger did not affect the UPDATE operation at all.

9.3.5 Modifying Data Using Triggers

Sometimes it is necessary to modify the parameters passed to the function for some reason. Many problems concerning the data inserted into a table can be solved by using CHECK constraints, but in some cases things are more complicated and cannot or should not be solved by restricting the data a user can insert into a table.

Let's take a look at a table storing delays of airplane flights. Assume that flights can never start too early. Sometimes users might want to insert delays using a negative value instead of a positive integer value. In this case you might not want to restrict the input to positive values, so you can define a trigger that substitutes the values passed to a function for a positive value. Before you see the trigger, you can take a look at the SQL code for creating the table for storing the delays:

 phpbook=# CREATE TABLE delay(number_of_flight text, minutes_delay int4); CREATE

If the table has been generated successfully, it is time to write the function that is called by the trigger:

 CREATE OR REPLACE FUNCTION moddata () RETURNS opaque AS '         BEGIN                 IF      NEW.minutes_delay < 0 THEN                         NEW.minutes_delay = NEW.minutes_delay * (-1);                         RAISE NOTICE ''delay modified'';                 END IF;                 RETURN NEW;         END; ' LANGUAGE 'plpgsql';

If the content of minutes_delay is lower than zero, the absolute value of the data passed to the function is generated and assigned to the NEW.minutes_delay field.

In the next step you can write the code of the trigger used for executing the function:

 CREATE TRIGGER trig_moddata BEFORE INSERT OR UPDATE         ON delay FOR EACH ROW         EXECUTE PROCEDURE moddata();

This time the trigger must be fired before performing an INSERT or an UPDATE operation because otherwise the operations would already be over when moddata is called. In this scenario the trigger must be fired for every INSERT and UPDATE operation. If UPDATE was not the reason for calling the trigger, negative values could easily be inserted by modifying the data in the table.

After inserting the trigger and the function into the database, you can perform an INSERT operation to see what is going to happen:

 phpbook=# INSERT INTO delay VALUES ('IB 0979', -34); NOTICE:  delay modified INSERT 19617 1

A message is displayed by the RAISE NOTICE command and a record has been added to the table. The table contains exactly one record now:

 phpbook=# SELECT * FROM delay;  number_of_flight | minutes_delay ------------------+---------------  IB 0979          |            34 (1 row)

As you can see, the value in the second column is not negative because it has been modified by the function called by the trigger.

9.3.6 Predefined Variables and Functions Called by a Function

In the section called "Predefined Variables" earlier in this chapter, you have already learned about predefined variables in PL/pgSQL. In this section it is worth taking a very brief look at the scope of these variables. In the next example you will see a function started by a trigger that calls an additional function:

 CREATE OR REPLACE FUNCTION insertmessage () RETURNS opaque AS '         BEGIN                 RAISE NOTICE ''TG_WHEN: %'', TG_WHEN;                 EXECUTE ''SELECT dismessage()'';                 RETURN NEW;         END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION dismessage () RETURNS int4 AS '         BEGIN                 RAISE NOTICE ''TG_WHEN: %'', TG_WHEN;                 RETURN 0;         END; ' LANGUAGE 'plpgsql';

Here is the code defining the trigger. Before inserting this trigger into the database, make sure that no triggers are defined on INSERT operations on data.

 CREATE TRIGGER trig_insertmessage AFTER INSERT         ON data FOR EACH ROW         EXECUTE PROCEDURE insertmessage();

The function called by the trigger displays the content of TG_WHEN and calls a function called dismessage. The target of the example is to see if dismessage can still see TG_WHEN:

 phpbook=# INSERT INTO data VALUES('a message'); NOTICE:  TG_WHEN: AFTER NOTICE:  plpgsql: ERROR during compile of dismessage near line 2 NOTICE:  Error occurred while executing PL/pgSQL function insertmessage NOTICE:  line 3 at execute statement ERROR:  parse error at or near "TG_WHEN"

As you can see, functions called by functions cannot see predefined variables. This is important because you have to pass the values you will need in dismessage to the function manually.

9.3.7 Endless Loops

This section presents some more examples on triggers. In these examples, we present some PostgreSQL specifics and see what can be done to avoid errors.

Loops are one of the most important things you have to take care of. Recall the content of the table called logtable. As you have seen, a trigger has been defined on data, but what happens when a trigger is defined on logtable?

Let's take a look at the code of logfunc again:

 CREATE OR REPLACE FUNCTION logfunc () RETURNS opaque AS '         BEGIN                 RAISE NOTICE ''trigger logfunc has been fired'';                 INSERT INTO logtable(tstamp, message, mestype)                         VALUES(now(), ''data has been inserted'', 0);                 RETURN NEW;         END; ' LANGUAGE 'plpgsql';

The definition of the trigger is shown in the next listing.

 CREATE TRIGGER trig_logfunc AFTER INSERT         ON logtable FOR EACH ROW         EXECUTE PROCEDURE logfunc();

The trigger will call logtable every time a value is inserted into the database. Because logfunc will also insert a record into the table, a trigger will be fired again. PostgreSQL will run into an endless loop:

 phpbook=# INSERT INTO logtable (tstamp, message, mestype) VALUES(now(), 'hello world', 3); NOTICE:  trigger logfunc has been fired NOTICE:  trigger logfunc has been fired NOTICE:  trigger logfunc has been fired

The execution has to be stopped manually by using Ctrl+C; otherwise, PostgreSQL will be caught in an endless loop. But if the loop is stopped manually, what will happen to the data in the table?

 phpbook=# SELECT * FROM logtable;  id |            tstamp             |        message         | mestype ----+-------------------------------+------------------------+---------   1 | 2001-11-12 19:20:41.270717+01 | an error message       |       3   2 | 2001-11-12 19:20:41.270717+01 | the second error       |       3   3 | 2001-11-13 08:08:12.244384+01 | data has been inserted |       0 (3 rows)

Nothing has happened to the table because the entire loop is processed in one transaction and a rollback has been performed when stopping the loop. This way nothing will happen to the data in the table. However, programmers must take care of endless loops because otherwise problems can easily occur, as you have seen in this example.

9.3.8 Triggers and Inheritance

One thing many people are not sure of is what happens when triggers are used in combination with inheritance. Therefore we have decided to include a special section about inheritance and triggers.

Let's create three tables that inherit from each other and store the SQL code in a file called creature.sql:

 CREATE TABLE creature (id int4, name text); CREATE TABLE human (gender char(1)) INHERITS (creature); CREATE TABLE worker (income int4) INHERITS (human);

The target is to store information about creatures. Creatures usually have a name and a unique identifier. Human beings are special creatures who can also have a gender the rest of the attributes of a human can be inherited from creature. Workers are special humans who have an additional attribute called income.

 [postgres@athlon postgres]$ psql phpbook < creature.sql CREATE CREATE CREATE

Let's take a look at the table called worker and see which fields are in the table:

 phpbook=# \d worker           Table "worker"  Column |     Type     | Modifiers --------+--------------+-----------  id     | integer      |  name   | text         |  gender | character(1) |  income | integer      |

As you can see, worker has all fields of creature and human plus one additional field for storing the income.

In the next step you can start inserting some data into the tables by using simple INSERT commands.

 phpbook=# INSERT INTO creature VALUES(1, 'Kika'); INSERT 21663 1 phpbook=# INSERT INTO worker VALUES(2, 'Paul', 'm', 290000); INSERT 21684 1

If creature is queried, all common columns of all three tables will be displayed. The content of the result consists of all records in all three tables because creature is the parent table.

 phpbook=# SELECT * FROM creature;  id | name ----+-------   1 | Kinka   2 | Paul (2 rows)

If worker is queried, only one record will be retrieved:

 phpbook=# SELECT * FROM worker;  id | name | gender | income ----+------+--------+--------   2 | Paul | m      | 290000 (1 row)

The behavior of PostgreSQL databases has changed in version 7.1. In versions prior to 7.1, the way inheritance has been treated was changed significantly.

Let's create a table where the triggers you are going to build store some logging information:

 phpbook=# CREATE TABLE triglog(tstamp timestamp, tgname name, tgrelname name); CREATE

The table consists of three columns. The first column stores a timestamp. The second column contains the name of the trigger that causes PostgreSQL to insert the record into the table. The third column contains the name of the table the trigger has been fired for.

Let's write a function that will be executed by the triggers:

 CREATE OR REPLACE FUNCTION triglogfunc () RETURNS opaque AS '         BEGIN                 RAISE NOTICE ''TG_NAME: %'', TG_NAME;                 RAISE NOTICE ''TG_RELNAME: %'', TG_RELNAME;                 INSERT INTO triglog VALUES (now(), tg_name, tg_relname);                 RETURN NEW;         END; ' LANGUAGE 'plpgsql';

The function displays some debugging information on the screen and inserts the data into the table called triglog. This way it is an easy task to find out which trigger was fired when and for what reason.

Let's take a look at the code of the trigger:

 CREATE TRIGGER trig_logtrig AFTER INSERT         ON creature FOR EACH ROW         EXECUTE PROCEDURE triglogfunc();

The trigger is executed after every INSERT statement performed on table creature. Both the function and the trigger can easily be inserted into the database by storing the code in a file and sending it to the psql as shown in the next listing:

 [postgres@athlon postgres]$ psql phpbook < trigger.sql CREATE CREATE

If no error occurred, you can see if the trigger works by inserting one record into the table the trigger has been defined for:

 phpbook=# INSERT INTO creature VALUES(3, 'Daisy'); NOTICE:  TG_NAME: trig_logtrig NOTICE:  TG_RELNAME: creature INSERT 21689 1

No surprises the function has been executed by the trigger and the desired information has been displayed on the screen. What happens if data is inserted into worker? As you have seen in the code, worker is the child of human, which is the child of creature:

 phpbook=# INSERT INTO worker VALUES(4, 'Anna', 'f', 500000); INSERT 21691 1

No trigger has been fired because the trigger has not been inherited by the table: Child tables inherit only columns, not functions. Knowing this is important because it helps you to design your applications and you will need it in order not to forget to define triggers on inherited tables. However, the data has successfully been inserted into worker:

 phpbook=# SELECT * FROM worker;  id | name | gender | income ----+------+--------+--------   2 | Paul | m      | 290000   4 | Anna | f      | 500000 (2 rows)

Let's create a second trigger. This time the trigger is defined on human and executes the same function as the previous trigger. To avoid confusion, the trigger is fired when an UPDATE operation is performed:

 CREATE TRIGGER trig_logtrig2 AFTER UPDATE         ON human FOR EACH ROW         EXECUTE PROCEDURE triglogfunc();

Let's perform an UPDATE operation on worker and see if the trigger is fired. Worker is a child of human, but the trigger is not executed:

 phpbook=# UPDATE worker SET income=280000 WHERE id=2; UPDATE 1

Let's see if the trigger is fired when data is inserted into the parent table of human:

 phpbook=# UPDATE creature SET name='Alan' WHERE id=2; UPDATE 1

Still no trigger fired. As you can see, triggers are not fired when the parent table is modified. As we have already mentioned, triggers cannot be inherited by other functions and don't affect parents.

Finally, let's take a look at the content of triglog:

 phpbook=# SELECT * FROM triglog;             tstamp             |    tgname    | tgrelname -------------------------------+--------------+-----------  2001-11-16 10:20:56.934013+01 | trig_logtrig | creature (1 row)

One record has been added to the table because PostgreSQL fired the trigger only when the table the trigger was defined for was modified.