Thread: Trigger of Transaction

Trigger of Transaction

From
Juan Pablo Cook
Date:
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.

I appreciate your help a lot.

Thanks ;)

JP Cook


Re: Trigger of Transaction

From
Joe Van Dyk
Date:
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. 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