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 Sat, 06 Feb 2010 22:07:17 GMT
Rick, thanks as always. All this makes sense. My recent emails about
trigger behaviors are to see where we can avoid having before and
after copies of the LOB columns in the triggering table so we don't
run into OOM when it can be avoided.

Since the only way to pass before and old values to the stored
procedure in trigger action is through formal arguments, then I guess
we can safely assume that before and after values of LOB columns from
triggering tables will never make their way into stored procedure. The
Derby Reference manual says "Note: Data-types such as BLOB, CLOB, LONG
VARCHAR, LONG VARCHAR FOR BIT DATA, and XML are not allowed as
parameters in a CREATE PROCEDURE statement."

So based on the fact that stored procedure gets before and old values
through parameters and LOBs can't be passed as parameters to stored
procedure, there is no need for us to keep before and after values of
LOB columns from the triggering table when the trigger action is a
stored procedure.

thanks,
Mamta

On Sat, Feb 6, 2010 at 7:42 AM, Rick Hillegas <Richard.Hillegas@sun.com> wrote:
> Hi Mamta,
>
> Some comments inline...
>
> Mamta Satoor 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);
>>    }
>>
>
> The above procedure looks like the right way to code a procedure which
> receives arguments from a trigger.
>>
>> 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.
>>
>
> The trigger does not establish a context which allows you to retrieve
> parameters this way. The SQL standard doesn't provide any support for this
> usage. It is possible that you could write some tricky logic which looks up
> the stack, cracks open the Derby runtime structures, and retrieves the
> parameters via techniques which are not part of Derby's public api. But you
> can't use SQL language to find the parameter values. The supported technique
> is to pass the values as formal arguments.
>
> Hope this helps,
> -Rick
>>
>> 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