RE: EXEC pROCEDURE IN TRIGGER

From: Guang Mei <GMei_at_ph.com>
Date: Wed, 29 Jun 2005 10:05:48 -0400
Message-ID: <7E62D965D357694C993D4F0E13B3C39B0585A9CF@phexchange.ph.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Hallas, John, Tech Dev
Sent: Wednesday, June 29, 2005 6:36 AM
To: oracle-l_at_freelists.org
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.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 29 2005 - 10:11:24 CDT