Class | PLRuby::Description::Trigger |
In: |
plruby.rb
|
Parent: | Object |
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 :
an hash containing the values of the new table row on INSERT/UPDATE actions, or empty on DELETE.
an hash containing the values of the old table row on UPDATE/DELETE actions, or empty on INSERT
An array of the arguments to the procedure as given in the CREATE TRIGGER statement
The following keys are defined
The name of the trigger from the CREATE TRIGGER statement.
The name of the relation who has fired the trigger
The object ID of the table that caused the trigger procedure to be invoked.
An array containing the name of the tables field.
The constant PL::BEFORE, PL::AFTER or PL::UNKNOWN depending on the event of the trigger call.
The constant PL::ROW or PL::STATEMENT depending on the event of the trigger call.
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();