Re: Any reason not to have logic in the db?

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
--
http://www.freelists.org/webpage/oracle-l