db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Access to old and new values of the triggering table's columns inside the trigger action when trigger action is a stored procedure...
Date Mon, 08 Feb 2010 19:15:01 GMT
BTW, I realized that in the create stored procedure, I accidentally
said NO SQL as shown below
       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'");
I changed the code to fix that but as expected, still got the error
about trying to directly reference the new value for triggering table
directly inside the stored procedure. The new definition of stored
procedure is as follows
        s.execute("create procedure proc_display_old_new " +
        		"(p1 char(1), p2 char(1)) parameter style java language "+
        		"java READS SQL DATA external name "+
        		"'org.apache.derbyTesting.functionTests.tests.lang.reproOldNewValuesInStoredProcedure.proc_display_old_new'");

I am reattaching the repro as reproOldNewValuesInStoredProcedureVer2,java

thanks,
Mamta


On Fri, Feb 5, 2010 at 2:05 PM, Mamta Satoor <msatoor@gmail.com> wrote:
> 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