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