Issue while call BEFORE trigger on postgresql

Question

i have a jdbc client communicate with a postgresql database. From JDBC a create a prepared statement for INSERT some data on a table, but at the moment of the creation of query, client doesn't know all field to insert inside statement, and for complete the query i've write a TRIGGER that fires BEFORE insert on that table, find the missing value, the continue to execute query. this is jdbc code:

String MY_INSERT = "INSERT INTO myTable VALUES(?, ?, ?, ?, ?, ?)";
PreparedStatement pstm = connection.PrepareStatement(MY_INSERT);
pstm5.setInt(1, 2562);
pstm5.setInt(2, 22);
pstm5.setInt(3, 0); //unknown value, set to zero
pstm5.setString(4, "a string");
pstm5.setInt(5, 0); //the other unknown value

this is the trigger code:

CREATE FUNCTION perform_() RETURNS trigger AS $myTrigger$

DECLARE 
vend VARCHAR(20);
pre numeric(10,2);

BEGIN
    SELECT field3, field5 FROM otherTable WHERE id = NEW.field2 INTO vend, pre;

    NEW.field3 = vend;
    NEW.field5 = pre;

    RETURN NULL;
END;
$myTrigger$ LANGUAGE plpgsql;

CREATE TRIGGER myTrigger
BEFORE INSERT ON myTable
EXECUTE PROCEDURE perform_();

when the trigger fires i have this error:

Exception: JDBCClass error: ERROR: record "new" is not assigned yet
Details: The tuple structure of a not-yet-assigned record is indeterminate.
SELECT field3, field5 FROM otherTable WHERE id = NEW.field2 INTO vend, pre;

what does it mean? record new it's assigned as you can see into jdbc code...

roman · Accepted Answer · 2013-08-16 09:20:26Z

1

First, you've missed FOR EACH ROW in trigger declaration:

CREATE TRIGGER myTrigger
BEFORE INSERT ON myTable
FOR EACH ROW EXECUTE PROCEDURE perform_();

Second, you have to return NEW; if you want your record to be inserted into your table

see sql fiddle demo

answered Aug 16, 2013 at 9:20

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue while call BEFORE trigger on postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related