Thomas Hill <[email protected]> writes:
>> Hi Thomas,
>>
>> I think you'd need to add a REFERENCING clause to the trigger definition
>> and pass in the new value as an argument to the procedure. Something
>> like:
>>
>> CREATE TRIGGER "TR_XY"
>> AFTER INSERT
>> ON "TBL_XY"
>> REFERENCING NEW AS NEW
>> FOR EACH ROW
>> CALL "SP_xy"('xyz', 0, NEW."RowID")
>>
>> Hope this helps,
>>
>
>
> Hi,
>
> referencing NEW as NEW I could add although not sure why this is needed
> (e.g. NEW as UPDATEROW or something, but NEW as NEW?).
Right, the first "NEW" is a keyword required by the syntax¹, and the
second "NEW" is a correlation name which could be any identifier. This
should work too:
CREATE TRIGGER "TR_XY"
AFTER INSERT
ON "TBL_XY"
REFERENCING NEW ROW AS whatever
FOR EACH ROW
CALL "SP_xy"('xyz', 0, whatever."RowID")
(the ROW keyword is optional, and might make it clearer that NEW is not
an identifier)
> Is passing the value in the only option?
> For compatibility with how other data
> base backends (PostgreSQL) do this I would prefer
> if there would be an option to access the value
> from within the procedure.
> Can someone confirm whether this is possible or not in Derby please?
The only way I can think of, is if the RowId column is an identity
column, you can call the IDENTITY_VAL_LOCAL function² from the stored
procedure. See the attachment for an example. One limitation with that
approach is that the function only works if the INSERT statement inserts
a single row at a time.
Hmm... Looking at it again, that won't work either, as the return value
of the IDENTITY_VAL_LOCAL function isn't updated until after the INSERT
statement has completed, which is after the trigger action has
completed. So the value seen in the stored procedure is actually that of
the row inserted by the previous statement, not the current one.
I don't have any other ideas, I'm afraid.
¹ http://db.apache.org/derby/docs/dev/ref/rrefsqlj89752.html
² http://db.apache.org/derby/docs/dev/ref/rrefidentityvallocal.html
--
Knut Anders
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class StoredProc {
public static void main(String[] args) throws SQLException {
Connection c = DriverManager.getConnection("jdbc:derby:memory:db;create=true");
Statement s = c.createStatement();
s.execute("create procedure p () language java parameter style java external name 'StoredProc.p' reads sql data");
s.execute("create table t (id int generated always as identity)");
s.execute("create trigger tr after insert on t for each row call p()");
s.execute("insert into t values default");
s.execute("insert into t values default");
s.execute("insert into t values default");
s.execute("insert into t values default");
}
public static void p() throws SQLException {
try (Connection c = DriverManager.getConnection("jdbc:default:connection");
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("values identity_val_local()")) {
if (rs.next()) {
System.out.println("Inserted row with ID=" + rs.getInt(1));
}
}
}
}
