Thread: Statement level trigger clarification

I'm new to triggers and I'm having difficulty in understanding how
statement level triggers on before updates work.

I have a function that sets new.last_modified := current_timestamp;

If I were to define a trigger as:-

CREATE TRIGGER my_trigger
  BEFORE INSERT OR UPDATE
  ON my_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE my_function();

and my update statement were to update more than one row, would I be
correct in understanding that every row the update statement touches
will have the exact same value for last_modified?


Chris
--
Chris Velevitch
Manager - Adobe Platform Users Group, Sydney
m: 0415 469 095
www.apugs.org.au

Adobe Platform Users Group, Sydney
September meeting: It's Going To Be Brilliant
Date: Mon 29th September 6pm for 6:30 start
Details and RSVP on http://apugs2008september.eventbrite.com

Chris Velevitch wrote:
> I'm new to triggers and I'm having difficulty in understanding how
> statement level triggers on before updates work.
>
> I have a function that sets new.last_modified := current_timestamp;
>
> If I were to define a trigger as:-
>
> CREATE TRIGGER my_trigger
>   BEFORE INSERT OR UPDATE
>   ON my_table
>   FOR EACH STATEMENT
>   EXECUTE PROCEDURE my_function();
>
> and my update statement were to update more than one row, would I be
> correct in understanding that every row the update statement touches
> will have the exact same value for last_modified?

No, this is not going to work at all.  NEW and OLD are not available in
statement triggers.  So your function will fail.

Peter Eisentraut wrote:
> Chris Velevitch wrote:

>> I have a function that sets new.last_modified := current_timestamp;

Remember that current_timestamp is stable across the lifetime of a
transaction; it'll return the same value each time it is called. Given
that, you can just use it in a row-level trigger, knowing that a batch
of records updated by the same transaction (which to the database is "at
the same time") will get the same timestamp.

There are also time functions that return the real wall clock, and the
clock at the time of the start of the statement rather than the
transaction. See:

http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

You do still pay the overhead of invoking a PL/PgSQL trigger for each
record altered/inserted, but there doesn't seem to be much of a way
around that. It's an area where a `DEFAULT FORCE' qualifier would be
nice - something to ignore user-submitted input and overwrite it with
the default parameter, probably a function call. At present there's
nothing like that (as far as I know) so you need to use a row-level trigger.

--
Craig Ringer