Re: [Q] User-defined function usage in trigger WHEN clause?

Youri Podchosov wrote:
>
> I'm posting this on behalf of my wife, Olga.
>
> Hi!
>
> It was an attempt to fire a trigger only if pkg_a.var == 'A' for the
> current transaction using WHEN clause. pkg_a.var was set before
> triggering event and all grants and synonyms were in place.
>
> 1. First try was,naturally:
> CREATE or REPLACE ....
> FOR EACH ROW
> WHEN ( ... and pkg_a.var='A')
>
> It did not work, but nobody promised. So I turned to functions.
>
> 2. First I created a function within the package and a pragma for all
> restrictions but 'read package variable'. The function was
>
> function fn_var return char AS
> return( var );
> end fn_var;
>
> and trigger WHEN looked like:
> WHEN ( ... and pkg_a.fn_var='A')
>
> When I tried to compile, it did not recognize <pkg_a.> expecting <new.>
> or <old.>
>
> 3.Then I decided to add the last restriction to pragma and made the
> function to return constant just to try if it will work at all,
> recompiled package and tried to recompile trigger. I got the same
> result.
>
> 4. I tried to pass new.tbl_field parameter to this function, changed
> everything, recompiled and got the same result.
>
> My questions are:
>
> - Why can I not use my function returning constant where I can use
> substr(new.tbl_field,1,1), for example ?
>
> - What might be a logical explanation, that I can not skip pragma
> restriction to read pkg var if I want to use package function this way
> ?
>
> I will appreciate your help and sharing your expertise.
>
> Regards, Olga.
>
>

Olga,

You are only allowed to put clauses that compare table columns to values in the WHEN condition of a table trigger. for example if a table A has columns x, y, z then the trigger can have a WHEN condition that uses x, y and z.

If you want to restrict execution of the body of the trigger based on a value returned by a function, then I suggest putting a IF..THEN..ENDIF statement aroung the body of the trigger.

Hope this helps,

Ashok

-- 
Ashok F. Kapur    | Galaxy Latin America |
Project Engineer  | (954)958-3373        |
afkapur_at_ccgate.hac.com