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

  • From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
  • To: andrew.kerber@xxxxxxxxx
  • Date: Tue, 12 Jun 2012 15:09:26 +0100

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!


Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767

Other related posts: