Re: Any reason not to have logic in the db?
Date: Tue, 12 Jun 2012 09:19:07 -0500
Message-ID: <CAJvnOJYA18YnjyLSZAtw3=k-FePxBsC6ii0p5-_rVHTxGFXqMw_at_mail.gmail.com>
Well, I am not that fond of large packages either, but thats just me. Like I said, I would put it back in the application code. To expand on this a little, if you store the business logic in the database, by default the DBA has configuration management responsibility for all the code in the database in addition to the data model and all the other standard database tasks. That can be a pain.
Most of my performance pain these days seems to come from java 'programmers' who call java objects, but have no idea what tables they are hitting in the database, or how they are hitting them. I use quotes because I dont consider them as really being programmers.
On Tue, Jun 12, 2012 at 9:09 AM, Norman Dunbar <oracle_at_dunbar-it.co.uk>wrote:
> Hi Andrew,
>
>
> On 12/06/12 14:57, Andrew Kerber wrote:
>
>> By the borderline call on the triggers, it depends on how the triggers are
>> used. I have seen 300 line+ triggers that really needed to be part of the
>> application code, because they changed frequently and were really for
>> business logic as opposed to database maintenance.
>>
> Ok, got you now. I was wondering how you would get the triggers to fire
> from the application - my bad!
>
> Any trigger more than a few [tens of] lines long should be hauled out into
> a package though, surely? Ok, I know it's not possible to use :NEW and/or
> :OLD within a package fired from a trigger, but parameter passing etc
> should reduce the need for huge triggers.
>
> I have a vague recollection from prehistoric times that trigger code was
> always parsed [hard] on every execution and should be kept to a minimum -
> hence, get it out in to a procedure/package call instead.
>
> Frequently changing triggers would be better extracted to
> procedures/packages anyway - wouldn't you say?
>
> <Aside> I once had vendor code that regularly hung itself. Usual procedure
> by the users was to reboot the application and the problem went away for a
> wee while. Eventually I got called in.
>
> A trigger was calling code in a package. The packaged code was calling out
> to an external procedure running from a C compiled library which connected
> back into the database as the same user that the original work was being
> done under, and then tried to update the same rows in he same table.
> Deadlock!
>
> I think most, if not all, of my performance and application problems are
> caused by vendors and/or developers who simply don't have a clue about how
> the database actually works. And "database agnostic" applications are the
> worst as they have to pander to the lowest common denominator over all the
> database backends it _could_ be used with.
>
> I love my job!
>
>
>
> Cheers,
> Norm.
>
>
> --
> Norman Dunbar
> Dunbar IT Consultants Ltd
>
> Registered address:
> Thorpe House
> 61 Richardshaw Lane
> Pudsey
> West Yorkshire
> United Kingdom
> LS28 7EL
>
> Company Number: 05132767
>
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 12 2012 - 09:19:07 CDT