trigger Before or After

From: avpro avpro <avprowebeden(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: trigger Before or After
Date: 2014-11-11 06:38:11
Message-ID: CAAQdDnnb9E89=RxZbu6Fdv1eLtM7bQG9CLAsmYWxKZarnzXRQg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

hi,

in the pgsql documentation
(http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html)

i haven't seen anything referring to: how is affected the data inserted in
the new table by a trigger Before Insert compared with a trigger After
Insert? and anything related to performance

for example:

tables: actuals (summarize the total running hours), log (the functional
hours are inserted in LOG as time)
function: sum
view: timeview (where running hours are calculated as a difference)

-- Function: sum()

-- DROP FUNCTION sum();

CREATE OR REPLACE FUNCTION sum()
RETURNS trigger AS
$BODY$begin
update actuals
set
hours = hours + (select time from time_view
where idlog = (select max(idlog) from timeview))
where actuals.idmac =
(SELECT idmac FROM selectedmac) ;
return new;
end$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION sum()
OWNER TO user;

--trigger
CREATE TRIGGER update_actuals_tg01
AFTER INSERT
ON log
FOR EACH ROW
EXECUTE PROCEDURE sum();

I read somewhere (I don't find the link anymore) that if the trigger is
After Insert, the data available in the table LOG might not be available
anymore to run the trigger. is that correct? or I might understood wrong?

what's the difference related to performance concerning a trigger Before
Insert compared with a trigger After Insert?

thank you
have a sunny day

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2014-11-11 08:19:43 Re: [GENERAL] trigger Before or After
Previous Message rahulk 2014-11-11 06:05:27 Integrating PostgreSQL with Elasticsearch and Kibana

Browse pgsql-performance by date

  From Date Subject
Next Message Albe Laurenz 2014-11-11 08:19:43 Re: [GENERAL] trigger Before or After
Previous Message Josh Berkus 2014-11-10 23:51:12 Re: Performance bug in prepared statement binding in 9.2?