The example shown in the last section is a round about way of tracking user modifications and deletions from/to the table. A better way to implement the idea of tracking user modifications and deletions is to use one function that will handle all three cases, ON INSERT, UPDATE , or DELETE . Also, one trigger should be used to call that function.

Note Delete trigger
 

Before going on any further, you should be aware that if you decide to create this function and trigger, you should remove the previously defined triggers so that you can truly test the ability of this trigger. If you do not delete the previous trigger examples, then those triggers will execute along with this trigger when it is invoked by an INSERT , DELETE , or UPDATE .

We defined an inv_track() function that handles the user modification and deletion tracking. This function uses an IF ELSE statement to specify:

  • when an on INSERT occurs, it assigns the current user to the user_aud column and assigns the current timestamp ( NOW ) to the mod_time

  • when an on UPDATE occurs, it does the same thing as on INSERT , which means that it assigns the new user name and time to the inventory table. However, it also adds the existing row in the inventory table to the inventory_audit table.

  • when an on DELETE occurs, it inserts the row to be deleted into the inventory_audit table.

The following checks for when a trigger is invoked on an INSERT , DELETE , and UPDATE :

 CREATE FUNCTION inv_track () RETURNS OPAQUE AS '
    BEGIN   
      
      --if a trigger insert or update operation occurs
      IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN

         NEW.user_aud := current_user;
         NEW.mod_time := ''NOW'';

        --inserts the row in the inventory 
        --table into the inventory_audit table
        INSERT INTO inventory_audit 
             SELECT * FROM inventory 
              WHERE prod_id=NEW.prod_id;
              
      RETURN NEW; 

      --else if a trigger delete operation occurs
      ELSE if TG_OP = ''DELETE'' THEN
     
        --inserts the row to be deleted
        --and the user and time the deletion occurred
        --into the inventory_audit table 
        INSERT INTO inventory_audit 
               SELECT *, current_user, ''NOW'' 
               FROM inventory WHERE prod_id=OLD.prod_id;

      RETURN OLD;
      END IF;
     END IF;
    END;
' LANGUAGE 'plpgsql';
   

Note Trigger Special Variables
 

The inv_track() function uses the special variables NEW , OLD , and TG_OP . If you would like to know more information about special variables that are defined for triggers, refer to the section called PL/pgSQL and Triggers in Chapter 9 , which is about the PL/pgSQL language.

The trigger that calls the inv_track function is simply defined as:

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

Currently, we are logged in as user william because he owns the inventory table. Only the table owner can define a trigger on the table (except for the superuser). Also, the inventory table contains the following data:

prod_id| descript  | amt_stock| user_aud |  mod_time
-------+-----------+----------+----------+---------------------
  123  | exam notes|       50 | jessica  | 2001-07-10 15:02:11-07
   

The inventory_audit table is shown below:

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)
   

Try inserting a column into the table to see if it automatically adds the user name and timestamp:

   INSERT INTO inventory VALUES (456, 'maps', 13);
   

Then log into another user and modify the amount in stock:

    \c booktown jessica
    UPDATE inventory SET amt_stock = 54 WHERE prod_id = 456;
   

View the inventory_audit table to make sure the old row from the inventory table was moved into the inventory_audit table.

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
    456 | maps     |        13 | william  | 2001-07-10 16:21:30-07 |          |
(3 rows)
   

Then view the inventory table to make sure the new user name and timestamp was recorded:

 prod_id |  descript  | amt_stock | user_aud |        mod_time
---------+------------+-----------+----------+------------------------
     123 | exam notes |        50 | jessica  | 2001-07-10 15:18:51-07
     456 | maps       |        54 | jessica  | 2001-07-10 16:21:28-07
(2 rows)
   

Lastly, log into another user name and remove the row from the inventory table:

   \c booktown mark
   DELETE FROM inventory WHERE prod_id = 456;
   

Take a look at the inventory_audit table to verify it recorded the old data and the user name who deleted the row:

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-10
12:03:53-07
    456 | maps     |        13 | william  | 2001-07-10 16:21:30-07 |          |
    456 | maps     |        54 | jessica  | 2001-07-10 16:21:28-07 | jessica  | 2001-07-10
16:25:18-07
(4 rows)