Re: Trigger question

Hi Keith,

    Interesting problem. Mutating triggers can be an issue if you try to solve your problem using only one row level trigger. The trick is to use a row level trigger, a statement level trigger, and a small package in between. The attached file contains my proposed solution. It seems to do the trick.

Good Luck
Rob Medley
medleyrk_at_iafrica.com

> I have to write a trigger for table A which goes like this ...
> >
> > TABLE A
> > id number
> > seq number
> > low number
> > hi number
> >
> > TABLE B
> > id number
> > low number
> > hi number
> >
> > Table A can have entries like
> >
> > 1,1,4,7 and 1,2,1,5.
> >
> > When Table A is changed (new row, update or delete) then the
> > range of low and hi must be copied to Table B. In the example
> > above, Table B should have values 1,1,7 for id,low and hi.
> >
> > If an update occurs, the minimum value of any low entries with
> > matching id's in Table A must be written to Table B. The
> > maximum value of any hi entries with matching id's in Table A
> > must be written to Table B. For deletes, Table B must also be
> > updated accordingly.
> >
> > How might I do this? (mutating table error seems to be the big
> > problem in this case)
>
> --
> Keith Shave
> Management Information Services
> Ph: (204) 945-7937
> Fax: (204) 948-2542