Thread

1. How to create a trigger

   Laurent Duperval <lduperval@microcelli5.com> — 2001-05-10T20:46:18Z

   Hello,
   
   I know nothing about triggers so I need a little hand-holding here. I'm
   taking an Oracle script and converting it to pgsql. One of the things
   that the Oracle database supports is a trigger on dates. So there are two
   fields in the table, CREATION_DATE and UPDATE_DATE.
   
   In the CREAT TABLE statement, I can set both dates using DEFAULT (once I
   figure out how to get today's date in). But hoiw would I write a trigger
   that changes the update_date to use the current time? Basically, I guess
   it would be
   
   create trigger date_update before update on mytable
       for each statement execut procedure [procedure that inserts a date
   for me in the update_date column]
   
   So what would I be doing for the portion in brackets?
   
   Thanks,
   
   L
   
   -- 
   Laurent Duperval <mailto:lduperval@microcelli5.com>
   
   Les plus vigoureux coups de main au destin n'aboutissent pas toujours dans la
   vie, aux démarrages qu'on attendait.
                                                -Alambic Talon
   
   

2. Re: How to create a trigger

   Thomas F.O'Connell <tfo@monsterlabs.com> — 2001-05-11T14:56:39Z

   > create trigger date_update before update on mytable
   >     for each statement execut procedure [procedure that inserts a date
   > for me in the update_date column]
   > 
   > So what would I be doing for the portion in brackets?
   
   
   at that point you need a function.
   
   check out the CREATE FUNCTION syntax in the docs 
   (http://www.postgresql.org/idocs/index.php?sql-createfunction.html).
   
   for what you were talking about, it seems like something as simple as
   
   create function trigger_before_update_mytable() returns opaque as '
   begin
   	new.update_date = now();
   	return new;
   end;
   ' as language 'plpgsql';
   
   would work.
   
   then your last line of the trigger statement would be:
   
   for each statement execut procedure trigger_before_update_mytable();
   
   good luck.
   
   -tfo
   
   
   
   
   
   

3. Re: Re: How to create a trigger

   Joseph Shraibman <jks@selectacast.net> — 2001-05-14T22:09:11Z

   See
   http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=10
   
   "Thomas F. O'Connell" wrote:
   > 
   > > create trigger date_update before update on mytable
   > >     for each statement execut procedure [procedure that inserts a date
   > > for me in the update_date column]
   > >
   > > So what would I be doing for the portion in brackets?
   > 
   > at that point you need a function.
   > 
   > check out the CREATE FUNCTION syntax in the docs
   > (http://www.postgresql.org/idocs/index.php?sql-createfunction.html).
   > 
   > for what you were talking about, it seems like something as simple as
   > 
   > create function trigger_before_update_mytable() returns opaque as '
   > begin
   >         new.update_date = now();
   >         return new;
   > end;
   > ' as language 'plpgsql';
   > 
   > would work.
   > 
   > then your last line of the trigger statement would be:
   > 
   > for each statement execut procedure trigger_before_update_mytable();
   > 
   > good luck.
   > 
   > -tfo
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 4: Don't 'kill -9' the postmaster
   
   -- 
   Joseph Shraibman
   jks@selectacast.net
   Increase signal to noise ratio.  http://www.targabot.com