Re: [GENERAL] Trigger of Transaction

On Mon, Apr 1, 2013 at 8:41 PM, Juan Pablo Cook <juampick(at)gmail(dot)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>.
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."

>
> I appreciate your help a lot.
>
> Thanks ;)
>
> JP Cook
>
>