While Jared is correct on the compilation issue there are other reasons why one would want to put the logic of a trigger into a separate procedure or function. Now I don't know about the rest of the list, but I hate long winded complex triggers. I prefer to put all of that into a procedure/package that the trigger can then call. This is especially true when your performing the same actions from several tables. Take an auditing task that one of out PeopleSoft developers was doing. She had created an audit table and 12 triggers of 12 different tables, all of which did the same logical thing. Now don't get me wrong, it worked, but when she had to add a column to the audit table there was a couple of days of chasing down triggers that had not been modified. And then there's the issue of having the audit record created even if the transaction fails which triggers don't handle, it's an all or nothing thing. Well a procedure/package can call "pragma autonomous_transaction" and get around that. So whereas the compilation issue is false there are other very valid reasons to follow the suggested course.
Again, I don't mean to pick on anyone, especially not Jared, but to educate.
Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA
__ __
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Jared Still Sent: Friday, July 01, 2005 12:31 PM To: Oracle-L Freelists Subject: Re: EXEC pROCEDURE IN TRIGGER
I would like to take this opportunity to point out that this is how Oracle myths get started and/or perpetuated.
You may read a lot of things about Oracle that may no longer be correct, or possibly never were correct.
In this case, as Mark Powell has shown, this was at one time true. Triggers were not compiled in Version 7 of Oracle.
I don't recall which version changed that, but the point is this: Oracle no longer works that way.
A clear cut case where the manual should have been consulted.
Not meaning to pick on anyone, just educate.
Jared
On 6/29/05, Guang Mei <GMei@(protected)> wrote:
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.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=us-ascii"> <META content="MSHTML 6.00.2800.1479" name=GENERATOR></HEAD> <BODY> <DIV dir=ltr align=left><SPAN class=165534016-01072005><FONT face=Arial color=#0000ff size=2>While Jared is correct on the compilation issue there are other reasons why one would want to put the logic of a trigger into a separate procedure or function. Now I don't know about the rest of the list, but I hate long winded complex triggers. I prefer to put all of that into a procedure/package that the trigger can then call. This is especially true when your performing the same actions from several tables. Take an auditing task that one of out PeopleSoft developers was doing. She had created an audit table and 12 triggers of 12 different tables, all of which did the same logical thing. Now don't get me wrong, it worked, but when she had to add a column to the audit table there was a couple of days of chasing down triggers that had not been modified. And then there's the issue of having the audit record created even if the transaction fails which triggers don't handle, it's an all or nothing thing. Well a procedure/package can call "pragma autonomous_transaction" and get around that. So whereas the compilation issue is false there are other very valid reasons to follow the suggested course.</FONT></SPAN></DIV> <DIV dir=ltr align=left><SPAN class=165534016-01072005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV dir=ltr align=left><SPAN class=165534016-01072005><FONT face=Arial color=#0000ff size=2>Again, I don't mean to pick on anyone, especially not Jared, but to educate.</FONT></SPAN></DIV> <DIV dir=ltr align=left><SPAN class=165534016-01072005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV dir=ltr align=left><SPAN class=165534016-01072005> <P><FONT size=2>Dick Goulet<BR>Senior Oracle DBA<BR>Oracle Certified 8i DBA</FONT> </P></SPAN></DIV><BR> <DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left> <HR tabIndex=-1> <FONT face=Tahoma size=2><B>From:</B> oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <B>On Behalf Of </B>Jared Still<BR><B>Sent:</B> Friday, July 01, 2005 12:31 PM<BR><B>To:</B> Oracle-L Freelists<BR><B>Subject:</B> Re: EXEC pROCEDURE IN TRIGGER<BR></FONT><BR></DIV> <DIV></DIV>I would like to take this opportunity to point out that this is <BR>how Oracle myths get started and/or perpetuated.<BR><BR>You may read a lot of things about Oracle that may no longer<BR>be correct, or possibly never were correct.<BR><BR>In this case, as Mark Powell has shown, this was at one time<BR>true. Triggers were not compiled in Version 7 of Oracle.<BR><BR>I don't recall which version changed that, but the point is this:<BR>Oracle no longer works that way.<BR><BR>A clear cut case where the manual should have been consulted.<BR><BR>Not meaning to pick on anyone, just educate.<BR><BR>Jared<BR><BR><BR> <DIV><SPAN class=gmail_quote>On 6/29/05, <B class=gmail_sendername>Guang Mei</B > <<A href="mailto:GMei@(protected)">GMei@(protected)</A>> wrote:</SPAN> <BLOCKQUOTE class=gmail_quote style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204 ,204) 1px solid">I have read somewhere that when writing a trigger, one should not put all<BR >the code in the trigger, instead one should create a store procedure and<BR>call it from the trigger. Doing so would improve the performance, becuase <BR>oracle will re-compile the trigger code everytime when a trigger is fired.<BR>My question is if there is any paper/doc/study exist somewhere that compare<BR>the performance. For a typical trigger with say about 20 lines of code, how <BR>much improvement could one expect if putting the code into a procedure. I<BR>guess I could do some test on the system by myself (too lazy) , but wondering<BR>if I can get some rough idea. I looked around on the web but can not find <BR>any detailed paper out there.<BR><BR>Thanks.<BR><BR></BLOCKQUOTE></DIV><BR></BODY></HTML>
