RE: EXEC pROCEDURE IN TRIGGER
- From: Guang Mei <GMei@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 29 Jun 2005 10:05:48 -0400
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
Other related posts:
