Re: Too many cursors

From: Van Messner <vmessner_at_bestweb.net>
Date: Fri, 01 Dec 2000 01:05:18 GMT
Message-ID: <iPCV5.70$7d.6672@newshog.newsread.com>

Hello Mike K:

    Thanks for the input. Originally I tried to find the people with a deleted supervisor in a before row trigger. But of course that led to a mutating table error. The dbms_jobs solution is out for now. Regarding your solution 2, how would that work. When a person is set to inactive there may be multiple other people who had him as a supervisor. I may have to change from zero to n rows, which means I have to find them.

    There is a solution which uses a package and a temporary table to which I can write the affected rows in the before update trigger. But I was hoping for something more elegant than a package, a temporary table and several procedures.

Van

"Mike Krolewski" <mkrolewski_at_rosetta.org> wrote in message news:904dd3$24i$1_at_nnrp1.deja.com...
> In article <EegV5.32$5d.5152_at_newshog.newsread.com>,
> "Van Messner" <vmessner_at_bestweb.net> wrote:
> > I have a persons table which is self-referential. Persons may have a
> > supervisor. When I mark a person as inactive, I want to set the
 supervisor
> > column to null for any person who had the inactive one as his
 supervisor.
> >
> > The following procedure works and does what I want. I can execute it
 in
> > SQL*Plus. Also I can just pull out the SQL and it runs fine.
> >
> > CREATE OR REPLACE PROCEDURE CLEANUP
> > IS
> > BEGIN
> > UPDATE PERSONS_MSTR
> > SET person_supervisor = null
> > WHERE person_supervisor in (
> > SELECT person_supervisor
> > FROM PERSONS_MSTR
> > WHERE sysdate - person_date_made_inactive < 1
> > );
> > END CLEANUP;
> > /
> >
> > So I thought I'd use an after update statement trigger to execute the
> > procedure, since I thought that was legal.
> >
> > CREATE OR REPLACE TRIGGER LG_T_P
> > AFTER UPDATE ON PERSONS_MSTR
> > BEGIN
> > CLEANUP;
> > END LG_T_P
> >
> > But this generates an ORA-01000 too many cursors error.
> >
> > Any suggestions, remedies or clues?
> >
> > Thanks,
> >
> > Van
> >
> >

>

> The problem is the you have done a classic no-no. Your after-update
> trigger updates the same table. As each row is updated, the after-
> update trigger is issued, which causes more updates and more triggers,
> etc.

>

> The obvious solution is to move the 'cleanup' to be outside of the
> trigger. You could write a cron job to perform the task or use dbms_job
> to run this update. Once a day should be sufficient. If you need it
> more often -- set it up for every n minutes. This work especially well
> if you want to wait ( 1 day ) after setting the table. If no updates
> had occurred after the date was set, the trigger will not fire. With
> the external program, the action will occur with or without other
> updates.

>

> Solution 2 is to make the update more restrictive. It apears that you
> only need to clean up the table when the inactive date is set for a
> supervisor. Rather than check all records every time any record is
> changed, change the record when a supervisor's field is changed.
> Updates triggers can be activated on a particular field, set of
> fields , or condition in that field(s)

>
>
>

> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rosetta.org
> Ususual disclaimers

>
>

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Nov 30 2000 - 19:05:18 CST