Обсуждение: 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