Re: Question on use of triggers to call functions

This is an acceptable way of processing data in a trigger. The problem comes if you're trying to do summary calculations on the data that is being entered. For instance table a has ten rows that contain value x, table b has a row which stores the number of occurences of x in table a.

If you use a row level trigger on table a to insert the "select count(*) from a where value = 'x'" into table b you'll end up with a mutating table error because the count depends on the whether the row is committed. Oracle has some methods around this problem. You need to use a combination of row and transaction triggers. There are some examples in the documentation.

In article <89hgi6$f8l$1_at_nnrp1.deja.com>,   elizabeth_yuen_at_my-deja.com wrote:
> Hi,
>
> Here's the problem I'm having:
>
> I have a row-level trigger (tr_1) which is designed to pass parameters
> to a function (func_main). The func_main is designed to pass similar
> parameters to another function (either func_A or func_B). It passes
> parameters to either func_A or func_B depending on parameters which
 are
> passed by the trigger to func_main.
>
> Trigger (tr_1) (parameters passes XYZ or ABC)
> |
> |-->Function (func_main) (evaluates parameters)
> |
> |-->Function (func_A) (do for XYZ only)
> |-->Function (func_B) (do for ABC only)
>
> (func_A and func_B will return a value back to
> func_main so that it can finish processing)
>
> When the trigger fires, the func_main appears to be called
 sucessfully,
> initially. But does not complete because it fails to calling either
> func_A or func_B.
>
> I ran the func_main with the parameters I expect to pass by the
 trigger
> and it runs fine (goes through to either func_A or func_B and returns
> processing to func_main as designed). This is the case when I execute
> func_main directly.
>
> It is not successful when I'm using the trigger to pass the same
> paramters.
>
> (func_main, func_A, func_B is currently designed with a return value
 of
> 0 or -1. However, the trigger (tr_A) calling func_main is not
 actually
> using the return value, it is just being assigned to a variable in the
> trigger (tr_A).
>
> ie. (statement in trigger - tr_A)
> v_func_value := ccex.func_creditsummary_calc (param_A, param_B,
> param_C, param_D, param_E);
>
> Any ideas? Is the design feasible in Oracle?
>
> Thanks in advance to anyone who can help.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 02 2000 - 00:00:00 CST