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:06:20 GMT
Hi Knut, I agree about being intelligent in recognizing which columns
needs to have before and after copy in case of triggers.

I was just thinking that as an incremental approach to triggers, one
could catch couple simpler cases before catching all the cases
1)If there is no REFERENCING clause in the trigger, then no need to
have any before and after copies
2)If the trigger action is a stored procedure call, then no need to
have before and copies of LOB and other columns which can't be passed
as parameters to stored procedures.

On Sun, Feb 7, 2010 at 3:15 AM, Kristian Waagan <Kristian.Waagan@sun.com> wrote:
> Knut Anders Hatlen wrote:
>> Mamta Satoor <msatoor@gmail.com> writes:
>>> 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."
>> This limitation is likely to be lifted, though. See DERBY-4066.
>>> 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.
>> I haven't looked at the code, so I don't know how viable the different
>> approaches are, but I would think that looking at which columns the
>> different triggers actually reference is a more robust way to determine
>> which columns to copy into the before and after images. Then we would
>> save memory/copying for trigger actions that don't call stored
>> procedures too, and also for non-LOB types.
> I agree with Knut Anders on this one, it would be nice if we could determine
> which columns to copy.
> That said, as part of my current LOB work, I'm looking at how to avoid
> materializing (or objectifying) LOB columns for triggers. This will
> definitely help for the cases where the LOB columns aren't referenced /
> used, and it may also enable us to keep the values as streams in other cases
> where the LOB is actually used in the trigger.
> This work is still in the early phase, but I have been able to remove some
> of the work-arounds / fixes put in place earlier. It is not yet clear to me
> if this approach will get me anywhere, but I'll investigate further.
> Regards,
> --
> Kristian

View raw message