Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> [Q] User-defined function usage in trigger WHEN clause?

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

From: Youri Podchosov <ynp_at_ynp.dialup.access.net>
Date: 1997/06/30
Message-ID: <33B87B56.6BB07F15@ynp.dialup.access.net>#1/1

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:

I will appreciate your help and sharing your expertise.

Regards, Olga.

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

| Youri N. Podchosov (ynp) \\\ E-mail: ynp_at_ynp.dialup.access.net |
| UNIX Systems Administrator ))) WWW: http://ynp.dialup.access.net |
| The Davidsohn Group, NYC /// B:(212)208-0129, H:(718)234-4140 |
+----------------------------------------------------------------------+
Received on Mon Jun 30 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US