Re: Question on Insert Trigger

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 07 Mar 2008 18:10:30 -0800
Message-ID: <1204942225.938032@bubbleator.drizzle.com>


zigzagdna_at_yahoo.com wrote:
> On Mar 7, 2:17 pm, Serge Rielau <srie..._at_ca.ibm.com> wrote:

>> DA Morgan wrote:
>>> Serge Rielau wrote:
>>>> zigzag..._at_yahoo.com wrote:
>>>>> Thanks to all. not null in my code was just an example. My filtering
>>>>> logic is lot more complex and cannot implemented through a costraint.
>>>>> What I am asking is there a way to do filtering in the trigger if it
>>>>> cannot be done using EXCEPTIONS INTO statement.
>>>> Can you squeeze in a view? I.e. rename the table, create select-* view
>>>> over the renamed table using the original table name.
>>>> Then create an INSTEAD OF TRIGGER on the view to intercept the rows
>>>> you don't like. You can ignore them, save them away, or patch them up.
>>> Another person trying to put massive overhead where a simple
>>> constraint would suffice: Why?
>> I think the OP made it clear that your proposal works for his overly
>> simplified example, but not the complex real world issue he has.
>> I am in no position to judge how complex is too complex for a check
>> constraint and whether his (not revealed) logic applies.
>>
>> Cheers
>> Serge
>>
>> --
>> Serge Rielau
>> DB2 Solutions Development
>> IBM Toronto Lab

>
> Thanks for proposing views, I will look into them in future.
> Right now. I found solution to what I was doing. I still delete in the
> trigger (gotten rid of autonomous transaction so delete indeed takes
> place), but
> to overcome mutating table error of Oracle, I use 3 triggres as
> pointed on Ask Tom's web site:
> http://asktom.oracle.com/tkyte/Mutate/index.html
>
> Why Oracle forces you to use round about ways is beyond me, but 3
> triggers do let me delete rows during insert.
>
> In my case I cannot use check constraints, so trigger is the solution
> for me.
>
> Thanks to all for your valuable input.

Why you are coding this mess is beyond me. Has it occurred to you that it might be more effective to state the business rule you are trying to enforce rather than some inefficient solution to what is to us an uknown problem?

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Mar 07 2008 - 20:10:30 CST

Original text of this message