RE: EXEC pROCEDURE IN TRIGGER

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_ph.com> 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.                  

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