db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Access to old and new values of the triggering table's columns inside the trigger action when trigger action is a stored procedure...
Date Fri, 05 Feb 2010 22:05:17 GMT
Hi,

I have a test case where there is an UPDATE trigger defined on a
table. The trigger action for this UPDATE trigger is a stored
procedure call. The definition of the CREATE TRIGGER is as follows
        s.execute("create trigger test6tr2 no cascade before update of
char1 on testtabl1 " +
        	"REFERENCING NEW as n_row old as o_row for each ROW "+
        	"call proc_display_old_new(o_row.char1, n_row.char1)");
And the procedure invoked by the trigger is defined as follows. The
stored procedure takes 2 parameters
        s.execute("create procedure proc_display_old_new " +
        	"(p1 char(1), p2 char(1)) parameter style java language "+
        	"java  NO SQL external name "+
        	"'org.apache.derbyTesting.functionTests.tests.lang.reproOldNewValuesInStoredProcedure.proc_display_old_new'");

As shown above, the CREATE TRIGGER is using the REFERENCING clause to
allow access to old and new values of triggering table's columns.
When trigge is fired, the stored procedure for it's 2 parameters will
get the old and new value of column "CHAR1" and they are passed using
the alias provided by the REFERENCING clause. The code for stored
procedure is as follows.
    public static void proc_display_old_new(String p1, String p2)
throws SQLException {
    	System.out.println("Inside the procedure called by the BEFORE
TRIGGER action");
    	System.out.println("old value of triggering table's char column
passed as parameter to store proc " + p1);
    	System.out.println("new value of triggering table's char column
passed as parameter to store proc " + p2);
    }


My question is that can the stored procedure access the old and new
values by simply using them by their names in sql rather than having
to get the values as parameters. In other, should following stored
procedure be able to access the new value using values(n_row.char1) as
shown below:
    public static void proc_display_old_new() throws SQLException {
        System.out.println("Inside the procedure called by the BEFORE
TRIGGER action");
        System.out.println("Inside the procedure called by the BEFORE
TRIGGER action");
        System.out.println("old value of triggering table's char
column passed as parameter to store proc " + p1);
        System.out.println("new value of triggering table's char
column passed as parameter to store proc " + p2);
        System.out.println();

        System.out.println("Now see if you can look at it through
n_row alias of REFERENCING clause");
        Connection conn =
DriverManager.getConnection("jdbc:default:connection");
        PreparedStatement ps = conn.prepareStatement("values(n_row.char1)");
        ps.executeQuery();
        conn.close();
    }
I tried writing a procedure like above but got execution time error
saying there is no column as "N_ROW.CHAR1". The exact error is as
follows
The exception 'java.sql.SQLException: Column 'N_ROW.CHAR1' is either
not in any table in the FROM list or appears within a join
specification and is ou
tside the scope of the join specification or appears in a HAVING
clause and is not in the GROUP BY list. If this is a CREATE or ALTER
TABLE  statement then 'N_ROW.CHAR1' is not a column in the target
table.' was thrown while evaluating an expression.

To see the failure, I have attached a simple java program which is
attempting to look at the new value using PreparedStatement ps =
conn.prepareStatement("values(n_row.char1)");

thanks,
Mamta

Mime
View raw message