Topic awaiting preservation: The self mutating table

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

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 ]:.

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

(But what else can I do?)

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

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)

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).]

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 ]:.

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?

Nevermind I got it thanks....

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

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.

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