class PLRuby::Description::Trigger - RDoc Documentation

# class PLRuby::Description::Trigger

Trigger procedures are defined in Postgres as functions without arguments and a return type of trigger. In PLRuby the procedure is called with 4 arguments :

- new (hash, tainted)

  an hash containing the values of the new table row on INSERT/UPDATE actions, or empty on DELETE.

- old (hash, tainted)

  an hash containing the values of the old table row on UPDATE/DELETE actions, or empty on INSERT

- args (array, tainted, frozen)

  An array of the arguments to the procedure as given in the CREATE TRIGGER statement

- tg (hash, tainted, frozen)

  The following keys are defined

  - name

    The name of the trigger from the CREATE TRIGGER statement.

  - relname

    The name of the relation who has fired the trigger

  - relid

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

  - relatts

    An array containing the name of the tables field.

  - when

    The constant PL::BEFORE, PL::AFTER or PL::UNKNOWN depending on the event of the trigger call.

  - level

    The constant PL::ROW or PL::STATEMENT depending on the event of the trigger call.

  - op

    The constant PL::INSERT, PL::UPDATE or PL::DELETE depending on the event of the trigger call.

The return value from a trigger procedure is one of the constant PL::OK or PL::SKIP, or an hash. If the return value is PL::OK, the normal operation (INSERT/UPDATE/DELETE) that fired this trigger will take place. Obviously, PL::SKIP tells the trigger manager to silently suppress the operation. The hash tells PLRuby to return a modified row to the trigger manager that will be inserted instead of the one given in new (INSERT/UPDATE only). Needless to say that all this is only meaningful when the trigger is BEFORE and FOR EACH ROW.

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

    CREATE FUNCTION trigfunc_modcount() RETURNS TRIGGER AS '
        case tg["op"]
        when PL::INSERT
            new[args[0]] = 0
          when PL::UPDATE
              new[args[0]] = old[args[0]] + 1
          else
              return PL::OK
          end
          new
      ' LANGUAGE 'plruby';

      CREATE TABLE mytab (num int4, modcnt int4, descr text);

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

A more complex example (extract from test_setup.sql in the distribution) which use the global variable $Plans to store a prepared plan

    create function trig_pkey2_after() returns trigger as '
       if ! $Plans.key?("plan_dta2_upd")
           $Plans["plan_dta2_upd"] =
                PL::Plan.new("update T_dta2
                              set ref1 = $3, ref2 = $4
                              where ref1 = $1 and ref2 = $2",
                             ["int4", "varchar", "int4", "varchar" ]).save
           $Plans["plan_dta2_del"] =
                PL::Plan.new("delete from T_dta2
                              where ref1 = $1 and ref2 = $2",
                             ["int4", "varchar"]).save
       end

       old_ref_follow = false
       old_ref_delete = false

       case tg["op"]
       when PL::UPDATE
           new["key2"] = new["key2"].upcase
           old_ref_follow = (new["key1"] != old["key1"]) ||
                            (new["key2"] != old["key2"])
       when PL::DELETE
           old_ref_delete = true
       end

       if old_ref_follow
           n = $Plans["plan_dta2_upd"].exec([old["key1"], old["key2"], new["key1"],
    new["key2"]])
           warn "updated #{n} entries in T_dta2 for new key in T_pkey2" if n != 0
       end

       if old_ref_delete
           n = $Plans["plan_dta2_del"].exec([old["key1"], old["key2"]])
           warn "deleted #{n} entries from T_dta2" if n != 0
       end

       PL::OK
    ' language 'plruby';

    create trigger pkey2_after after update or delete on T_pkey2
     for each row execute procedure
     trig_pkey2_after();