> Hello all. I have a question about PL/SQL and Oracle. Right
now I have
> a table which holds info about users who play a game. Each
time they
> play I put a row in the database from a PL/SQL script. After
a LONG
> time this table may get too large, making my CGI scripts
which use this
> data contained within slow.
>
> So when I have x number of records I would like to:
>
> 1. Block the table.
> 2. SELECT all the records into a new table with a similar
name.
> 3. DELETE the records from the old table.
> 4. Release the table.
>
> I wrote the PLSQL code to do it in my script when I add a
record but it
> seems to be to be an ideal candidate for an ON BEFORE INSERT
> trigger ... My questions are - Since it's POSSIBLE that two
or more
> records can be added at a time AFTER the maximum number of
records is
> reached, causing the trigger to fire for both, which means
the table
> block will fail for at least one, so:
>
> 1. Will the record be added if the table block fails?
> 2. How can I send a message back to the script that's
inserting the
> record? (I don't think I can as it's a trigger and is
affiliated with
> the table and not with any specific script.
>
> I'm just curious if I should do it instead in my script ...
It just
> seems to scream "TRIGGER!" as it's an action that should
happen on
> every insert. (Count records, if necessary create new table,
copy
> records, delete records, etc.)
>
> Any help is much apprceiated!
> john
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
>
first, your approach is ok, but I personally believe archiving
should be a seperate process. this aleviates the overhead of
making the check every time there is an insert. i believe your
best bet is to place a job in the oracle job queue (dbms_jobs
package) that executes when your usage is at its lowest point.
if you do not have the jobs package installed placing this
function in a trigger is preferable since it will check every
time a row is inserted not just every time a row is inserted
with your script.
if you do choose to use a trigger note that oracle supports row
level locking so a full table lock is not necessary and people
will be able to insert into the table while the archive process
is active.
for the case that the two rows are inserted while the table
count is above the maximum. the second transaction will wait
for the locks to be released by the first process (so a commit
or rollback will be required as soon as possible)
and then effectively do nothing except insert the row (no
exception should be thrown).
as for passing a message back to the script etc. that inserted
the row whenever an error occurs (or some condition you can
detect programmatically) an exception is/can be thrown (you
can define and throw your own exceptions). these exceptions
will propagate outward (even from within a trigger) until they
are handled in an exception handler or get to the shell/program
that initiated the sql statement.
another method of passing information is to create a messaging
table and write data into the message table from the trigger
and then checking for messages from the executing script
immediately after the insert statement.
I still would strongly suggest keeping the archive process
seperate from the production process.
andre azaroff
aazaroff_at_redrose.net
--
This answer is courtesy of QuestionExchange.com
http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=6544&cus_id=USENET&qtn_id=4524
Received on Wed Oct 27 1999 - 21:12:52 CDT
