PL/R User's Guide - R Procedural Language
Prev | Home |  | Next

# Chapter 12. Trigger Procedures

Trigger procedures can be written in PL/R. PostgreSQL requires that a procedure that is to be called as a trigger must be declared as a function with no arguments and a return type of `trigger`.

The information from the trigger manager is passed to the procedure body in the following variables:

`pg.tg.name`

  The name of the trigger from the `CREATE TRIGGER` statement.

`pg.tg.relid`

  The object ID of the table that caused the trigger procedure to be invoked.

`pg.tg.relname`

  The name of the table that caused the trigger procedure to be invoked.

`pg.tg.when`

  The string `BEFORE` or `AFTER` depending on the type of trigger call.

`pg.tg.level`

  The string `ROW` or `STATEMENT` depending on the type of trigger call.

`pg.tg.op`

  The string `INSERT`, `UPDATE`, or `DELETE` depending on the type of trigger call.

`pg.tg.new`

  When the trigger is defined `FOR EACH ROW`, a data.frame containing the values of the new table row for `INSERT` or `UPDATE` actions. For triggers defined `FOR EACH STATEMENT` and for `DELETE` actions, set to `NULL`. The atribute names are the table's column names. Columns that are null will be represented as NA.

`pg.tg.old`

  When the trigger is defined `FOR EACH ROW`, a data.frame containing the values of the old table row for `DELETE` or `UPDATE` actions. For triggers defined `FOR EACH STATEMENT` and for `INSERT` actions, set to `NULL`. The atribute names are the table's column names. Columns that are null will be represented as NA.

`pg.tg.args`

  A vector of the arguments to the procedure as given in the `CREATE TRIGGER` statement.

The return value from a trigger procedure can be `NULL` or a one row data.frame matching the number and type of columns in the trigger table. `NULL` tells the trigger manager to silently suppress the operation for this row. If a one row data.frame is returned, it tells PL/R to return a possibly modified row to the trigger manager that will be inserted instead of the one given in `pg.tg.new`. This works for `INSERT` and `UPDATE` only. Needless to say that all this is only meaningful when the trigger is `BEFORE` and `FOR EACH ROW`; otherwise the return value is ignored.

Here's a little example trigger procedure that forces an integer value in a table to keep track of the number of updates that are performed on the row. For new rows inserted, the value is initialized to 0 and then incremented on every update operation.

    CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS '

        if (pg.tg.op == "INSERT")
        {
          retval <- pg.tg.new
          retval[pg.tg.args[1]] <- 0
        }
        if (pg.tg.op == "UPDATE")
        {
          retval <- pg.tg.new
          retval[pg.tg.args[1]] <- pg.tg.old[pg.tg.args[1]] + 1
        }
        if (pg.tg.op == "DELETE")
          retval <- pg.tg.old

        return(retval)
    ' LANGUAGE plr;

    CREATE TABLE mytab (num integer, description text, modcnt integer);

    CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
        FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');

Notice that the trigger procedure itself does not know the column name; that's supplied from the trigger arguments. This lets the trigger procedure be reused with different tables.

Prev | Home | Next
R Function Names |  | License