Closed Thread Icon

Topic awaiting preservation: The self mutating table Pages that link to <a href="https://ozoneasylum.com/backlink?for=12514" title="Pages that link to Topic awaiting preservation: The self mutating table" rel="nofollow" >Topic awaiting preservation: The self mutating table\

 
Author Thread
Maskkkk
Paranoid (IV) Inmate

From: Willaimsport, PA, US of A the hole in the Ozone
Insane since: Mar 2002

posted posted 11-21-2002 00:48

Somethings wrong with my trigger in oracle, it keeps telling me that SCOTT.ENROLLMENT is mutating, and the trigger/function may not see it....

What the heck does that mean?



Maskkkk

- Face the Present

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 11-21-2002 00:58

Are you trying to modify or query the table that is activating the trigger?

Oracle has no love for kind of silly business.




.:[ Never resist a perfect moment ]:.

Maskkkk
Paranoid (IV) Inmate

From: Willaimsport, PA, US of A the hole in the Ozone
Insane since: Mar 2002

posted posted 11-21-2002 02:04

Yes..sigh...I am....

(But what else can I do?)

Maskkkk
Paranoid (IV) Inmate

From: Willaimsport, PA, US of A the hole in the Ozone
Insane since: Mar 2002

posted posted 11-21-2002 02:44

Wait, does an alias of a table make a copy of it?

(My teacher says so...)

*Maskkkk runs off to double check, in hopes that someone on the ozone will know....



Maskkkk

- Face the Present

Maskkkk
Paranoid (IV) Inmate

From: Willaimsport, PA, US of A the hole in the Ozone
Insane since: Mar 2002

posted posted 11-21-2002 03:10

Say what the heck is a virtual table?

It says here in the oriely book that it creates one of these when you create an alias for a table, but it doesn't say if it is an actually copy or not....

(I just kinda thought it would solve my problem)

Maskkkk
Paranoid (IV) Inmate

From: Willaimsport, PA, US of A the hole in the Ozone
Insane since: Mar 2002

posted posted 11-21-2002 03:40

Welp nope that's not it, same error.... perhapes I should go into more detail....

I have a Student TABLE, a enrollment TABLE, and an Offering TABLE




(See my poor excuse for an ERD (above) )

Now what I want to do is make a trigger that after an insert into enrollment, runs a select to count how many people there are in the Offering that the new enrollment was inserted into.

(I.E. Run a select on enrollment with a count(*) to count the number of records)


If the size of the enrollment after the insert is larger than that of the Offerings classSizeLimit, Delete the class,


Sounds simple enough to me, but apparently it can't be done because the trigger has a LOCK (or something) on the Enrollment table, and thus can't delete the record.

I tried doing the trigger on the Enrollment table before the new record was inserted, but I don't know how to stop the record from being inserted Inserted into the enrollment table, before this happens.


HELP! PLEASE HELP!
*SOB* *SOB*


-A very weak and tired

Maskkkk


[This message has been edited by Maskkkk (edited 11-21-2002).]

[This message has been edited by Maskkkk (edited 11-21-2002).]

[This message has been edited by Maskkkk (edited 11-21-2002).]

[This message has been edited by Maskkkk (edited 11-21-2002).]

[This message has been edited by Maskkkk (edited 11-21-2002).]

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 11-21-2002 05:22

I'm assuming this all has to be done in oracle?

This is the kind of thing I'd usually do with a couple of queries. Count first and then do the insert.

Hmm In Oracle though you should be able to do the count query first with an IF statement before the insert.



.:[ Never resist a perfect moment ]:.

Maskkkk
Paranoid (IV) Inmate

From: Willaimsport, PA, US of A the hole in the Ozone
Insane since: Mar 2002

posted posted 11-21-2002 14:01

By query do you mean using a CURSOR? Cause that's what I'm using, is there a better way to do it then the good old fashion CURSOR and FETCH?

Maskkkk
Paranoid (IV) Inmate

From: Willaimsport, PA, US of A the hole in the Ozone
Insane since: Mar 2002

posted posted 11-21-2002 21:59

Nevermind I got it thanks....

I switched the placement of some of the data and it worked.

brucew
Paranoid (IV) Inmate

From: North Coast of America
Insane since: Dec 2001

posted posted 11-22-2002 05:26

Way off topic, but every time I've seen this thread's name, my eyes see, "Self Mutilating Table".

Or maybe it's just too many black pills.

In any event, I would direct the orderlies to take any self-mutilating tables to the basement for a little therapy.

Maskkkk
Paranoid (IV) Inmate

From: Willaimsport, PA, US of A the hole in the Ozone
Insane since: Mar 2002

posted posted 11-22-2002 05:31

Heck yeah any table that self-mutilates it's self is in serious need of some pills.

« BackwardsOnwards »

Show Forum Drop Down Menu