RE: EXEC pROCEDURE IN TRIGGER

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Wed, 29 Jun 2005 11:09:16 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF41050395B984@usahm236.amer.corp.eds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guang Mei
Sent: Wednesday, June 29, 2005 10:06 AM
To: oracle-l_at_freelists.org
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_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
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 29 2005 - 11:15:33 CDT