RE: EXEC pROCEDURE IN TRIGGER
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Jun 2005 11:09:16 -0400
In earlier versions of the documentation Oracle recommended that a
trigger not exceed 72 lines of code. On version 7.0 of the database
triggers were not compiled so trigger code was more like anonymous code
and had a higher run time cost compared to stored procedures and
packaged code.
But somewhere along the line Oracle has changed triggers to be stored
code. From the 9.2 Concepts manual >> Oracle stores PL/SQL triggers in
compiled form, just like stored procedures.<<
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Guang Mei
Sent: Wednesday, June 29, 2005 10:06 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: EXEC pROCEDURE IN TRIGGER
I have read somewhere that when writing a trigger, one should not put
all the code in the trigger, instead one should create a store procedure
and call it from the trigger. Doing so would improve the performance,
becuase oracle will re-compile the trigger code everytime when a trigger
is fired.
My question is if there is any paper/doc/study exist somewhere that
compare the performance. For a typical trigger with say about 20 lines
of code, how much improvement could one expect if putting the code into
a procedure. I guess I could do some test on the system by myself (too
lazy), but wondering if I can get some rough idea. I looked around on
the web but can not find any detailed paper out there.
Thanks.
Guang
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Hallas, John, Tech
Dev
Sent: Wednesday, June 29, 2005 6:36 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: EXEC pROCEDURE IN TRIGGER
Oracle does not allow a commit (or rollback or savepoint)in a trigger.
Your procedure must have an implicit commit because of the use of DDL.
This is allowed in SLQ obviously but explains why it fails when called
from a trigger.
What exactly is the contents of the procedure. Can you work around it?
John
************************************************************************
*
PRIVILEGED AND CONFIDENTIAL:
This communication, including attachments, is for the exclusive use of
addressee and may contain proprietary, confidential and/or privileged
information. If you are not the intended recipient, any use, copying,
disclosure, dissemination or distribution is strictly prohibited. If
you are not the intended recipient, please notify the sender immediately
by return e-mail, delete this communication and destroy all copies.
************************************************************************
*
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
