Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted or updated.

Why is that? And is there a way to prevent it from happening?

begin;                                                                                              

create table foos (state text not null);                                                            

create function f() returns trigger as $$ begin                                                                                
  -- This seems to be ran after all the rows are inserted or updated?                             
  -- expected it to run once for each row as it was inserted or updated.                            
  raise notice '%', count(*) from foos where state = 'initial';                                     
  return null;                                                                                      
end $$ language plpgsql;                                                                            

create trigger f after insert or update on foos for each row execute procedure f();                 

insert into foos select 'initial' from generate_series(1, 5);                                      
update foos set state = 'canceled';

Expected Output:

psql:/tmp/t.sql:14: NOTICE:  1
psql:/tmp/t.sql:14: NOTICE:  2
psql:/tmp/t.sql:14: NOTICE:  3
psql:/tmp/t.sql:14: NOTICE:  4
psql:/tmp/t.sql:14: NOTICE:  5
INSERT 0 5
psql:/tmp/t.sql:15: NOTICE:  4
psql:/tmp/t.sql:15: NOTICE:  3
psql:/tmp/t.sql:15: NOTICE:  2
psql:/tmp/t.sql:15: NOTICE:  1
psql:/tmp/t.sql:15: NOTICE:  0
UPDATE 5

Actual Output:

psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
psql:/tmp/t.sql:14: NOTICE:  5
INSERT 0 5
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
psql:/tmp/t.sql:15: NOTICE:  0
UPDATE 5

Edit:

Using a before trigger behaves differently (and how I would expect it to behave):

begin;                                                                                              

create table foos (state text not null);                                                            

create function f() returns trigger as $$ begin                                                     
  raise notice '%', count(*) from foos where state = 'initial';                                     
  return new;                                                                                       
end $$ language plpgsql;                                                                            

create trigger f before insert or update on foos for each row execute procedure f();                

insert into foos select 'initial' from generate_series(1, 5);                                       
update foos set state = 'canceled';

Output:

psql:/private/tmp/t.sql:14: NOTICE:  0
psql:/private/tmp/t.sql:14: NOTICE:  1
psql:/private/tmp/t.sql:14: NOTICE:  2
psql:/private/tmp/t.sql:14: NOTICE:  3
psql:/private/tmp/t.sql:14: NOTICE:  4
INSERT 0 5
psql:/private/tmp/t.sql:15: NOTICE:  5
psql:/private/tmp/t.sql:15: NOTICE:  4
psql:/private/tmp/t.sql:15: NOTICE:  3
psql:/private/tmp/t.sql:15: NOTICE:  2
psql:/private/tmp/t.sql:15: NOTICE:  1
UPDATE 5
share|improve this question
    
I know for SQL Server that is by design. The design decision for this behavior maximizes trigger performance and therefore I doubt it can be changed. Having said.that, I have no PostgreSQL experience! –  Max Vernon Nov 7 '12 at 1:16
    
The only work around would be to execute each statement separately. Perhaps using a cursor or some other loop etc. –  Max Vernon Nov 7 '12 at 1:18
add comment

2 Answers

I guess the documentation says it: http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html

If the trigger fires after the event, all changes, including the effects of other triggers, are "visible" to the trigger.

Anyone know if it's possible to avoid this behavior somehow?

share|improve this answer
1  
Nope, but you can use another BEFORE trigger and control their ordering. Triggers are executed in name order, lexically sorted (alphabetical). So place the trigger where you want it in the execution ordering. –  Craig Ringer Nov 7 '12 at 1:14
add comment

Since the your INSERT and UPDATE are atomic (either all rows inserted/updated or none), you cannot catch a state when the count will not be 5 or 0. First the INSERT happens, then the trigger is fired for all the rows.

share|improve this answer
2  
Then why does using a before trigger behave differently? –  Joe Van Dyk Nov 6 '12 at 22:27
    
@JoeVanDyk this is a very good question causing me hard time. –  dezso Nov 7 '12 at 15:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.