Trigger of Transaction

On Mon, Apr 1, 2013 at 8:41 PM, Juan Pablo Cook <juampick@gmail.com> wrote:

Hi everyone! I need your help with this problem.

I'm using PostgreSQL *9.2 Server* & the latest jdbc
driver: postgresql-9.2-1002.jdbc4.jar

I have a many to one relation. I have this piece of code:

con.setAutoCommit(false); //transaction block start

// Make an insert to one table (Vector)
// After that I insert the childs of the first table with their parent_id
like the FK.
con.commit(); //transaction block end

I have this Trigger:

CREATE *TRIGGER *trigger_update_index *AFTER INSERT*
ON "Vector" FOR EACH ROW
EXECUTE PROCEDURE update_index();

CREATE OR REPLACE FUNCTION *update_index*() RETURNS *TRIGGER *AS
$update_index$
DECLARE
BEGIN
-- Make something
END;
$update_index$ LANGUAGE plpgsql;

What's the problem? that when the trigger fire only the Parent (Vector)
was inserted an not the childs :S so I need that records to be inserted to
work in my function.

I'm trying to make a Trigger, only to *execute after ALL the transaction*.
So, after all the INSERTs INTO (like 5 or 10) I want to launch my function.
I found some information in google about this: "*Constraint Trigger*",
that perhaps I can tell some rules before triggering but I don't know if it
is what I need to and also don't know how to code that.

create constraint trigger my_trigger_name

after insert on products

deferrable

for each row

execute procedure blah();

"constraint" triggers let you change when the trigger executes.

http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html says "When
the CONSTRAINT option is specified, this command creates a *constraint
trigger*. This is the same as a regular trigger except that the timing of
the trigger firing can be adjusted using SET
CONSTRAINTS<http://www.postgresql.org/docs/9.2/static/sql-set-constraints.html&gt;.
Constraint triggers must be AFTER ROW triggers. They can be fired either at
the end of the statement causing the triggering event, or at the end of the
containing transaction; in the latter case they are said to be *deferred*.
A pending deferred-trigger firing can also be forced to happen immediately
by using SET CONSTRAINTS. Constraint triggers are expected to raise an
exception when the constraints they implement are violated."