Re: Question on Insert Trigger

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. Received on Fri Mar 07 2008 - 17:03:17 CST