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';

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 VernonNov 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 VernonNov 7 '12 at 1:18

2 Answers
2

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.

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 RingerNov 7 '12 at 1:14