Writing Triggers

Triggers are used to call predefined functions after certain events. Let's assume a real-world example. We use a database to store all sales of a shop. Whenever a product is bought, the price, the number of products sold, and the name of the products have to be inserted into a table. Because we want to know the exact time we sold the products, we create a trigger that inserts the current time into a certain column automatically.

Our table looks like this:

 shop=#  \   d   shop  Table "shop"  Attribute    Type     Modifier -----------+-----------+----------  prodname   text        price      integer     amount     integer     sold       timestamp 

Before an example of a trigger is presented, look at the online help available about triggers:

 yourdb=#  \   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 ) 

Triggers define the kind of action that has be performed after or before events such as INSERT or UPDATE operations. Usually a function is executed by the trigger.

Here are the code of the function and the code of the trigger:

  CREATE FUNCTION timetrigger() RETURNS opaque AS '   BEGIN   UPDATE shop SET sold=''now'' WHERE sold IS NULL;   RETURN NEW;   END;   ' LANGUAGE 'plpgsql';   CREATE TRIGGER inserttime AFTER INSERT   ON shop FOR EACH ROW EXECUTE   PROCEDURE timetrigger();  

First, the function used by the trigger has to be implemented. After that, the trigger for a certain event can be created. In this case, the trigger is fired after an INSERT operation on shop . We use the now function to get the current time.

Let's see what happens when inserting data into the table:

  INSERT INTO shop (prodname,price,amount)   VALUES('Knuth''s Biography',39,1);  

Here is the result:

  SELECT * FROM shop ;  prodname      price  amount          sold -------------------+-------+--------+------------------------  Knuth's Biography    39        1  2001-03-05 01:49:03+01 (1 row) 

You can see that a correct timestamp can be found in shop.sold .

Note

Trigger procedures are created as functions with no arguments and a return type of opaque . opaque is a datatype that is used only in combination with triggers. It can be compared to void (C datatype).

Automatically Created Variables

Triggers create special variables when being executed. This section gives you a short overview of these variables. It is taken from the official PostgreSQL manual and has been adopted slightly.:

• NEW holds the new database row on INSERT / UPDATE statements on row-level triggers. The datatype of NEW is record .

• OLD contains the old database row on UPDATE / DELETE operations on row-level triggers. The datatype is record .

• TG_NAME contains the name of the trigger that is actually fired (datatype name ).

• TG_WHEN contains either AFTER or BEFORE (see the syntax overview of triggers shown earlier).

• TG_LEVEL tells whether the trigger is a ROW or a STATEMENT trigger (datatype text ).

• TG_OP tells which operation the current trigger has been fired for ( INSERT , UPDATE , or DELETE ; datatype text ).

• TG_RELID contains the object ID (datatype oid ), and TG_RELNAME (datatype name ) contains the name of the table the trigger is fired for.

• TG_RELNAME contains the name of the table that caused the trigger invocation. The datatype of the return value is name .

• TG_ARGV[] contains the arguments from the CREATE TRIGGER statement in an array of text.

• TG_NARGS is used to store the number of arguments passed to the trigger in the CREATE TRIGGER statement. The arguments themselves are stored in an array called TG_ARGV[] (datatype array of text ). TG_ARGV[] is indexed starting with .

Some of these variables are helpful for debugging trigger functions. TG_OP , for example, can be used to find out why a trigger has been executed and what event caused the trigger to be fired.