Home > mailing lists

Thread: Trigger of Transaction

Trigger of Transaction

From

Juan Pablo Cook

Date:

02 April 2013, 06:41:37

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:

02 April 2013, 18:30:31

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