Re: [SQL] Advice for generalizing trigger functions

[Erik Jones]

On Dec 25, 2007, at 6:20 PM, Richard Broersma Jr wrote:
I've created quite a few functions that log modifications to  
various history tables. (the history table has the same name as the  
base table but is prefixed by the 'History.' schema.) The only  
difference between functions I can find is the table name.

Is there any way to generalize these myriad of functions into one?


Below is a sample of a typical logging trigger function.

Regards,
Richard Broersma Jr.


CREATE OR REPLACE FUNCTION "project"."log_managers_ops"()
RETURNS trigger AS
$BODY$
BEGIN


     IF TG_OP IN ( 'UPDATE', 'DELETE' ) THEN

          UPDATE History.Managers AS M
             SET endts = now()
           WHERE M.manager_id = OLD.manager_id
             AND now() BETWEEN M.startts AND M.endts;

     end IF;


     IF TG_OP IN ( 'UPDATE', 'INSERT' ) THEN

     INSERT INTO History.Managers
          VALUES ( now()::timestamptz, 'INFINITY'::timestamptz,  
NEW.*);

          RETURN NEW;

     END IF;

     RETURN OLD;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

TG_TABLE_NAME will have the name of the table the trigger was fired  
on.  With that and using EXECUTE for your INSERT statements, you'll  
probably be set.

Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings