On Mon, Apr 1, 2013 at 8:41 PM, Juan Pablo Cook <juamp...@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>. 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 > >
