Oracle-L: RE: Trigger or ????

From: Vergara, Michael (TEM) <mvergara_at_guidant.com>
Date: Tue, 14 May 2002 08:58:28 -0800
Message-ID: <F001.004602B6.20020514085828@fatcity.com>

Here's a thought...create a stored procedure and run it from the DBMS_JOB scheduler. Start the job at 23:55, and have it loop internally (checking sysdate) until exactly the time you want, then reset the sequence. Be sure to set the DBMS_JOB.NEXT parameter so that it starts at exactly the same time every day.

Another idea is to run your reset script from 'cron' or 'at' and again let the system determine when exactly to fire the reset.

Cheers,
Mike

-----Original Message-----
Sent: Tuesday, May 14, 2002 7:58 AM
To: Multiple recipients of list ORACLE-L

I have a need to reset a sequence number at 00:01 everyday. I thought about creating a trigger to check the time, but thought that there might be a better way than checking the time every time a record is being added. I also thought about checking the max date on the table and comparing against the system date. When system date > max then reset the sequence number. I like this logic better and thought of holes with using the time.

The only problem I have is that this seems like a lot of overhead every time I add a record, which will be often. Is a trigger the only method available to me?

Thanks,

Laura

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Vergara, Michael (TEM)
INET: mvergara_at_guidant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue May 14 2002 - 11:58:28 CDT