Thread: How to create a trigger
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
> 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
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