A trigger returns the type opaque. Opaque data type is used only by internal functions that call other functions. Functions defined to return type opaque cannot be used in sequel queries. This means that functions used in a trigger cannot be defined in the SQL language because SQL does not support type opaque.

The syntax to create a trigger is:

   CREATE TRIGGER 

trigger_name

 {BEFORE|AFTER} 

event_type

     ON 

table_name

 FOR EACH {ROW|STATEMENT}
     EXECUTE PROCEDURE 

function_name

 (arguments)

Note Creating a Trigger
 

Only the table relation owner can create a trigger on the table relation.

When executing the create trigger command, it adds a new trigger with that name into the current database. The trigger will have an association to the relation table and execute the function specified. The events possible for a trigger are one of INSERT, DELETE, or UPDATE. The trigger can be defined to execute BEFORE/AFTER an operation or event is attempted on a row.

Warning Order Matters
 

If you create a trigger, then create the function that the trigger calls, the trigger will NOT execute properly. You should first create the function, then create a trigger that uses the function.

You can use triggers to check values entered to make sure they are between the allowed range. Usually, this can be performed by a constraint check, but these examples can help you have a basic understanding of how triggers work.

For instance, this function checks the author identification numbers and makes sure that the number entered for the inserted or updated author is not left blank (which is NULL) or less than 100:

Example 5-25. PL/pgSQL check_a_id() function

  
   CREATE FUNCTION check_a_id() RETURNS OPAQUE AS '
    BEGIN
     -- checks to make sure the author id
     -- inserted is not left blank or less than 100 
        IF NEW.a_id ISNULL THEN
           RAISE EXCEPTION 
           ''The author id cannot be left blank!'';
        ELSE
           IF NEW.a_id < 100 THEN
              RAISE EXCEPTION 
              ''Please insert a valid author id.'';     
           ELSE
           RETURN NEW; 
           END IF;
        END IF;
    END;
' LANGUAGE 'plpgsql';
   

Note Using Quotes
 

When dealing with text data values in PL/pgSQL, they need to be placed inside a set of two single quotes. This applies to messages printed with the RAISE commands. For more information on PL/pgSQL syntax and usage, refer to the sect1 entitled "Programming in PL/pgSQL" in Part VI.

If you use double quotes, PostgreSQL will not recognize the string as a message to be printed to the screen. In PostgreSQL, a set of double quotes helps it to recognize a object name as case sensitive. For more information about naming conventions and the case structure, refer to Part III.

We can now create a trigger which uses the check a_id to check the author identification numbers after a new row is inserted or an update occurs on the author table:

Example 5-26. Creating a Trigger

   CREATE TRIGGER if_author_added
   BEFORE INSERT OR UPDATE ON author 
      FOR EACH ROW
          EXECUTE PROCEDURE check_a_id();
   

Test the trigger to see if it works. Use the following to insert into a_id a number less than 100:

UPDATE author SET a_id = 12 
       WHERE lastname = 'Brown';
   

The following message appears:

ERROR:  Please insert a valid author id.
   

Try updating the author table and setting an author id to nothing.

   INSERT INTO author (lastname, firstname, email)
          VALUES ('test', 'test', 'test');
   

You will receive the following message:

ERROR:  Author identification is blank!
   

Note Unique to PostgreSQL
 

CREATE TRIGGER is also unique to PostgreSQL and may not be supported by other database systems.

Another example of using a trigger is to track every user that inserts or updates a record in a table. We first modified the inventory table to contain the user name who audited the table and the time it occurred. This command adds two columns to track the user performing the insert and the time of day the modification took place:

   ALTER TABLE inventory ADD COLUMN user_aud text;
   ALTER TABLE inventory ADD COLUMN mod_time timestamp;
   

Now create the function that will insert the user name and time into the columns when an insert is performed:

Example 5-27. PL/pgSQL inv_audit() function

   CREATE FUNCTION inv_audit () RETURNS OPAQUE AS '
    BEGIN

     -- Keeps track of who changed the table
     -- and when the change occurred.

        NEW.user_aud := current_user;
        NEW.mod_time := ''NOW'';
        
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';
   

Notice that the NOW timestamp is specified not within double quotes, but within two sets of single quotes. This is specific to the PL/pgSQL language. Also, this will find the current user and timestamp information and put it into the user_aud and mod_time column when it returns NEW. If the user supplies a different timestamp or current user, this will over ride it and insert the current time and user.

Note Defining a Function to be used by a Trigger
 

The function that is used by the trigger must be defined in a language that allows returning type opaque. The function will always return type opaque. Defining a function using the SQL language does not work because SQL does not support type opaque.

A trigger can now be created to call the inv_audit function. This trigger needs to be invoked by a BEFORE INSERT or UPDATE ON the inventory table:

Example 5-28. Creating a Trigger

    CREATE TRIGGER inv_audit BEFORE
           INSERT OR UPDATE ON inventory
              FOR EACH ROW EXECUTE PROCEDURE inv_audit();
   

Try testing this trigger by inserting some values into the inventory table:

    INSERT INTO inventory VALUES (123,'testbook',50);
   

Note Specifying column names
 

You do not need to specify the column name if you are inserting the values in the order the columns are listed. Although there are five columns in this table and we only specified 3 values, the last two columns will contain NULL values.

You can then use the SELECT * FROM inventory to view the result:

prod_id | descript | amt_stock | user_aud |        mod_time
--------+----------+-----------+----------+---------------------
    123 | testbook |        50 |  william | 2001-06-10 14:17:54-07
(1 row)
   

Notice that the username and the modification time was not inserted by the insert query above. The user name and modification time was automatically added to the table by the inv_audit trigger.

The previous trigger works fine, except for the problem that it only tracks the newest user who performed an update or insert on the table. If a user performs a DELETE or UPDATE on the table, the data stored before that UPDATE/DELETE occurred is now lost. This is because the trigger over writes the current data when an INSERT or UPDATE occurs.

To track modifications made to the table, we first need to create another table with the same structure as the original inventory table. Use the following to create a table called inventory_audit:

  SELECT * INTO inventory_audit FROM inventory; 
  

You can redefine your current trigger to use the inv_audit() function only when an INSERT occurs. Let's create a separate function to handle the data when a modification is made to the table, which is when an UPDATE occurs.

To change the definition of the inv_audit trigger, first drop the trigger, and then recreate it with only the INSERT option:

   DROP TRIGGER inv_audit ON inventory;

   CREATE TRIGGER inv_audit BEFORE
          INSERT ON inventory
          FOR EACH ROW EXECUTE PROCEDURE inv_audit();
   

The inv_audit () function does not need to change or be redefined because the inv_audit trigger will use that function on an UPDATE operation. Also, the function will exist before the trigger is created. That means the trigger can reference the object identification number of the inv_audit () function. Therefore, you do not need to remove the function and then re-create it.

After having created the trigger, define another function which is called when an UPDATE occurs. It will INSERT the current data held in the inventory table into the inventory_audit table before the data is over-written by a new user and timestamp. This function is defined as:

   CREATE FUNCTION inv_audit_mod () RETURNS OPAQUE AS '
    BEGIN

     -- Keeps a record or the last user who
     -- changed the table and when the 
     -- last change occurred.
 
          NEW.user_aud := current_user;
          NEW.mod_time := ''NOW'';

        -- This inserts the currently stored information 
        -- into the inventory_audit table 
        INSERT INTO inventory_audit 
        SELECT * FROM inventory WHERE prod_id=NEW.prod_id;
              
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';
   

Now create a second trigger which calls the inv_audit_mod() function when a user updates the information in the inventory table.

    CREATE TRIGGER inv_audit_mod BEFORE
           UPDATE ON inventory
           FOR EACH ROW EXECUTE PROCEDURE inv_audit_mod();
   

Test the first trigger by inserting some data into the table:

   INSERT INTO inventory VALUES (231, 'bookmark', 32, 'mark');
   

Notice that we did not specify the time this insert occurred. It is because we want the inv_audit trigger to automatically set that value. However, we tried to insert user name 'mark' into the user audit column, but it over-wrote that name and instead inserted the actual user who is executing the command, user william. This is exactly what we want it to do. If you execute a select * on the inventory table, you should see this:

prod_id| descript | amt_stock| user_aud |    mod_time 
-------+----------+----------+----------+--------------------
 123   | testbook |       50 | william  | 2001-07-10 14:17:54-07
 231   | bookmark |       32 | william  | 2001-07-10 14:38:00-07
(2 rows)
   

Or you can try logging into another database user name and then modifying something else to see if the second trigger logs the old user into the inventory_audit table and still update new information into the inventory table:

   \c booktown jessica

   UPDATE inventory 
      SET descript = 'exam notes' 
    WHERE prod_id = 123;
   

Note Switching into Another Database User
 

The psql command \c allows the user to switch to another database without having to quit the current database. To switch to another user, use the \c then specify the database name and the user name you want to connect as.

View the inventory_audit table to verify that it has the old data for product id 123:

prod_id | descript | amt_stock | user_aud |        mod_time
--------+----------+-----------+----------+---------------------
    123 | testbook |        50 | william  | 2001-07-10 14:17:54-07
(1 row)
   

The inventory table should now contain the new updated column and new modified time and user name jessica:

prod_id| descript  | amt_stock| user_aud |  mod_time
-------+-----------+----------+----------+---------------------
  231  | bookmark  |       32 | william  | 2001-07-10 14:38:00-07
  123  | exam notes|       50 | jessica  | 2001-07-10 15:02:11-07
(2 rows)
   

The two triggers work great, except for that it does not keep a record of the data in the table before a deletion occurrs. Also, when a user removes a row from the table, their name along with the time it occurred should be recorded.

To adapt the inventory table for these changes, you can add two more columns. One of the columns has a text field and the other has a time with time zone field. The next commands create the user_del and del_time columns:

   ALTER TABLE inventory ADD COLUMN user_del text;
   ALTER TABLE inventory 
         ADD COLUMN del_time timestamp with time zone;
   

We can use parts of the existing inv_audit_mod () function to define the new function that will be called by the trigger when a DELETE occurs. We want to keep the part where it inserts the row about to be modified into the inventory_audit table. However, PL/pgSQL has a different variable defined for an ON DELETE operation. It is called the OLD variable. It holds the old database row on an UPDATE/DELETE operation. Substitute the OLD variable into the location where the NEW variable is used.

The function is defined by:

 CREATE FUNCTION inv_audit_del () RETURNS OPAQUE AS '
    BEGIN

     -- Keeps a record of the last user who
     -- changed the table, 
     -- the time when the change occurred, and
     -- the user and time when the row was deleted.
 
        -- This inserts the currently stored information 
        -- into the inventory_audit table 

        INSERT INTO inventory_audit 
               SELECT *, current_user, ''NOW'' 
                     FROM inventory 
                     WHERE prod_id=OLD.prod_id;
              
        RETURN OLD;
    END;
' LANGUAGE 'plpgsql';
   

We did not initialize NOW and current user to the OLD variable because we want the time stamp and user name to be inserted into the user_del and del_time columns. Therefore, we specified the INSERT command to insert all columns from the inventory table (5 columns) into the inventory_audit table. Added to those columns are the user name and timestamp. Those two values fill the last two columns in the inventory_audit table.

Last of all, define a trigger to call that function when a delete occurs. The command below defines a trigger for the inv_audit_del function:

 CREATE TRIGGER inv_audit_del BEFORE
           DELETE ON inventory
           FOR EACH ROW EXECUTE PROCEDURE inv_audit_del();
   

To test the inv_aduit_del function, try deleting one of the rows inserted before:

   DELETE FROM inventory WHERE prod_id = 123;
   

If you view the inventory_audit table at this time, you will see the following:

prod_id | descript  | amt_stock | user_aud |        mod_time        | user_del |        del_time
--------+----------+-----------+----------+------------------------+----------+----------------------
    123 | testbook |        50 | william  | 2001-07-10 14:17:54-07
    231 | bookmark |        32 | william  | 2001-07-10 15:38:00-07 | jessica  | 2001-07-11
12:03:53-07
(2 rows)
   

Notice that:

  • the row to be deleted was copied into the inventory_audit table.

  • the user who deleted the column, jessica, was inserted into the user_del column.

  • the time the delete occurred was inserted into the del_time column.