RE: User-defined functions in WHEN clause of a trigger
Title: RE: User-defined functions in WHEN clause of a trigger Rich, Remove the when clause from trigger definition and put it inside. CREATE OR REPLACE TRIGGER my_trigger BEFORE UPDATE ON my_table FOR EACH ROW BEGIN If my_function = some_value then do_something_meaningful; end if; end; / Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: User-defined functions in WHEN clause of a trigger
Not sure but got a feeling that it has something to do with the Purity
Level of the function. Something you declare with PRAGMA RESTRICT_REFERENCES
For a function to be usable in the DMLs, it needs to satisfy a few purity
rules, which also need to be explicitly declared using PRAGMA
RESTRICT_REFERENCES. Guess this might be the case in triggers as well.
Regards
Naveen
-Original Message-
Sent: Wednesday, September 11, 2002 10:10 PM
To: Multiple recipients of list ORACLE-L
Hey all,
The subject says it all. In 8.1.7, can I:
CREATE OR REPLACE TRIGGER my_trigger
BEFORE UPDATE ON my_table
FOR EACH ROW
WHEN (my_function = 'SOME VALUE')
BEGIN
blah
END my_trigger;
Everytime I try to compile this, I get an ORA-4076: invalid NEW or OLD
specification. But I can specify a builtin function, e.g.:
WHEN (SYS_CONTEXT('USERENV','HOST') = 'MYHOST')
I've RTFM'd and tried Metalink as well, but I haven't been able to find
anything that says I can't use a user-defined function here.
Anyone?
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: User-defined functions in WHEN clause of a trigger
From the docs:
Restrictions:You can specify a trigger restriction only
for a row trigger. Oracle evaluates this condition for each row affected by the
triggering statement.You cannot specify trigger restrictions for INSTEAD
OF trigger statements.You can reference object columns or their
attributes, or varray, nested table, or LOB columns. You cannot
invoke PL/SQL functions or methods in the trigger
restriction.
Waleed
-Original Message-From: Jesse, Rich [mailto:[EMAIL PROTECTED]]Sent:
Wednesday, September 11, 2002 12:40 PMTo: Multiple recipients of list
ORACLE-LSubject: User-defined functions in WHEN clause of a
triggerHey all,The subject says it all. In 8.1.7, can
I: CREATE OR REPLACE TRIGGER
my_trigger BEFORE UPDATE ON
my_table FOR EACH
ROW WHEN (my_function = 'SOME
VALUE')
BEGIN
blah END
my_trigger;Everytime I try to compile this, I get an "ORA-4076: invalid
NEW or OLDspecification". But I can specify a builtin function,
e.g.: WHEN
(SYS_CONTEXT('USERENV','HOST') = 'MYHOST')I've RTFM'd and tried Metalink
as well, but I haven't been able to findanything that says I can't use a
user-defined function here.Anyone?Rich
Jesse
System/Database
Administrator[EMAIL PROTECTED]
Quad/Tech International, Sussex, WI USA--Please see the official
ORACLE-L FAQ: http://www.orafaq.com--Author: Jesse,
Rich INET: [EMAIL PROTECTED]Fat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051San
Diego, California -- Public Internet
access / Mailing
ListsTo
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).
RE: User-defined functions in WHEN clause of a trigger
OK, I need to make another bookmark...I got distracted by the Conditions link in the text just above the text you quoted. Thanks, Waleed! Not the answer I wanted, but it is the correct answer... :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, September 11, 2002 2:28 PM To: Multiple recipients of list ORACLE-L From the docs: Restrictions: You can specify a trigger restriction only for a row trigger. Oracle evaluates this condition for each row affected by the triggering statement. You cannot specify trigger restrictions for INSTEAD OF trigger statements. You can reference object columns or their attributes, or varray, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger restriction. Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
