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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxx> 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.