Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?

share|improve this question
    
Regarding your last question, you are after deferrable triggers. Unfortunately, the documentation says: 'This can only be specified for constraint triggers.' You have to put the logic of your trigger procedure into a 'normal' procedure and call it from your application. –  dezso Jul 31 '13 at 9:19

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
still doesn't work. Now i obtain a new error. The error is on query that fires trigger. Log told: cannot begin/end transaction in pl/pgsql. the context is line of trigger contains COMMIT command, and the statement that throw error is the insert command that fire trigger. –  giozh Jul 31 '13 at 9:06
    
@giozh you can't issue a COMMIT; inside a function. Just like BEGIN; and ROLLBACK; - all these are supposed to throw an error. –  dezso Jul 31 '13 at 9:17
1  
You cannot (and should not!) mess with the transaction established by the firing query. Committing inside a trigger seems like a very strange (if not wrong) thing to do. Any change done in the trigger will be committed by the transaction in which the trigger was fired. –  a_horse_with_no_name Jul 31 '13 at 9:30
    
ok, now it works. But if for some reason my jdbc client catch exception and call rollback after the trigger were fired, the trigger will execute the same (leaving database in inconsistent state) or not? because it looks like that triger doesn't wait commit command of jdbc. –  giozh Jul 31 '13 at 9:37
2  
Again: anything that is done inside the trigger is part of the current transaction (the one that is active when the trigger is fired) and thus will be committed or rolled back together with that transaction. That's the whole purpose of a transaction. –  a_horse_with_no_name Jul 31 '13 at 9:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.