Re: Question on Insert Trigger

From: DA Morgan <>
Date: Sun, 09 Mar 2008 21:14:49 -0700
Message-ID: <>

Serge Rielau wrote:
> DA Morgan wrote:

>> But I am wondering ... would you use a package variable, when
>> you get them into DB2, to enforce a business rule?

> Ah why not? Depends on your rule.
> E.g. if I have a rule that says: Ignore all input provided by user
> DAMORGAN that seems like a sensitive thing to do.

There you go Serge proving me correct yet again. Ignoring my input would be SENSIBLE ... not SENSITIVE. That rule should definitely be hard coded.

> Rules can be user dependent, can they not?

Not business rules. Business rules are things such as don't let the person exceed their credit limit. Don't create a duplicate account number. Now you might want to configure today's interest rate as a package variable captured by the package initialization section but it is a value ... not a rule. The "rule" would be to apply it to all transactions.

> You like to point out that certain products fail to provide BEFORE
> TRIGGERs. Aren't before triggers used to enforce business rules?

Yes though that is not the only reason they are used.

> By claiming that constraints suffice you say BEFORE TRIGGERs are not
> needed and you bite your own proverbial tail.

Not at all. But given the choice of enforcing a busines rule with a constraint or a trigger I would choose the constraint every time if it would suffice.

> BEFORE TRIGGERs are used where CHECK CONSTRAINTs fail, such as looking
> at data in different tables, or looking up variable content.

It is not a question of one or the other. It is a question of using the most efficient solution given the business problem. The most efficient is rarely the most complex. Are you suggesting that you are going to drop primary key constraints from DB2 in favor of triggers because one solution is good enough for all situations? <g>

>> With respect to your other question:

> So the answer is no, you are an "I", not a "We".

Certainly not royalty. Though a few years back I was at a hedge laying contest in the Cotswolds and was protected from Prince Charles by one of his bodyguards so that might count for something. <g> He's quite good, I might add, with an axe.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sun Mar 09 2008 - 23:14:49 CDT

Original text of this message