postgres+JDBC: fire trigger with non autocommit transaction

Question

i have a jdbc client that make an two INSERT query on database whitout autocommit, and a trigger on database that fires when a insertion is done on a certain table

public foo() throws MyException {
    String INSERT1 = "INSERT INTO table1 VALUES(?,?)";
    String INSERT2 = "INSERT INTO table2 VALUES(?)";
    try {
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement pstm1 = conn.prepareStatement(INSERT1);
        PreparedStatement pstm2 = conn.prepareStatement(INSERT2);
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

        pstm1.setString(1, "string1");
        pstm1.setString(2, "string2");
        pstm1.executeUpdate();

        pstm2.setString(1, "string3");
        pstm2.executeUpdate();

        conn.commit();
    }catch (ClassNotFoundException ex) {
        throw new MyException("foo error: " + ex.getMessage());
    } catch (SQLException ex) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException ex1) {
                throw new MyException("foo error: " + ex.getMessage());
            }
        }
    }catch (Exception e) {
        System.out.println(e.getMessage());
    } finally {
        try {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            }
            new CloseConnection(pstm1, pstm2, null, conn).closeAll();
        } catch (SQLException ex) {
            throw new MyException("foo error: " + ex.getMessage());
        }

and the trigger looks like

CREATE FUNCTION my_procedure() RETURNS TRIGGER AS $my_trigger$
DECLARE new_field VARCHAR(30);
BEGIN
    new_field = NEW.field;
    --field is a column of table 2
    --retrieve some data from table1 and table2 then execute some insert on 
    --another table
COMMIT;
END;
$my_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger
AFTER INSERT ON table2
EXECUTE PROCEDURE my_procedure(); 

now, everytime I execute the jdbc code, the insertion always fail. With some debug with println() I see that my code fail on

pstm2.executeUpdate();

but no exception was caught. I think it's a trigger problem that try to start execution when my jdbc transaction is not yet committed, in fact, the error on postgresql is

record new is not assigned yet

There's a way to fix it (start trigger when my trasaction is committed)?

Answer 1

Your trigger is fired for each statement not for each row. In statement level triggers you can not access the new and old records.

You need to change your create trigger to create a row-level trigger, rather than a statement level trigger:

CREATE TRIGGER my_trigger
  AFTER INSERT ON table2
  FOR EACH ROW -- this is the change
EXECUTE PROCEDURE my_procedure();

Triggers are always executed in the same transaction as the triggering statement.