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

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.

+----------------------------------------------------------------------+


| Youri N. Podchosov (ynp)    \\\    E-mail: ynp_at_ynp.dialup.access.net |

+----------------------------------------------------------------------+