Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

trigger at certain time

Posted on 2005-05-01
8
Medium Priority
?
2,854 Views
Last Modified: 2012-03-08
Hi, I have table registration(customer_id number, id_of_book number, last_time_customer_has_possibility_to_borrow_registrated_book date).

I need to create trigger, which triggers every day at one o'clock and do the following:

if last_date_of_borrowing > sysdate then delete from registration every row with last_time... > sysdate

thanks

0
Comment
Question by:xLeon
8 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 13905782
dbms_job
0
 
LVL 1

Accepted Solution

by:
Plaban_Patra earned 120 total points
ID: 13906833
Create a procedure proc_delete which will delete this
declare
job_id number;
begin
dbms_job.submit(job_id ,'proc_delete;',trunc(sysdate+1)+1/24,'trunc(SYSDATE,''MI'') + 1');
end;
/


0
 
LVL 9

Expert Comment

by:konektor
ID: 13907203
be sure you have set your system parameters :
job_queue_processes more than zero
job_queue_interval more than zero (only for 8i)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Assisted Solution

by:sujit_kumar
sujit_kumar earned 60 total points
ID: 13907382
plaban is correct you will have to submit a job which will do it for you. You can directly use a delete statement instead of any procedure. See below, this should do the task. One more thing always do a COMMIT after submitting/removing a job.

declare
job_id number;
begin
dbms_job.submit(job_id ,'begin delete from registration where last_borrow_registrated_book >  sysdate;commit; end;',trunc(sysdate+1)+1/24,'trunc(SYSDATE,''MI'') + 1');
commit;
end;
/

Sujit
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13910504
No, this is not a task for an Oracle trigger.  You can do this with a PL\SQL stored procedure that you run as a scheduled job as others have indicated.
0
 
LVL 1

Author Comment

by:xLeon
ID: 13913323
Thanks everybody,
I must accept Plaban's answer, because he was first who answered quite nice. If somebody can and have time, please write me something about semantics of dbms_job.submit(,,,) and it's arguments. What means job_id? proc_delete are probably some sql commands. And what means  trunc(sysdate+1)+1/24,'trunc(SYSDATE,''MI'') + 1'?

thansk
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13917872
"Job_id" is just a number for the job.  Every job needs a unique number.

"Proc_delete" must be the name of the PL\SQL procedure you created to do the deletes.

The next parameter "trunc(sysdate +1)+1/24" sets the time the job should run for the first time.  Trunc(sysdate) = today at midnight.  The "+1" means add one day (or tomorrow at midnight). The "+1/24" means add one twenty-fourth of a day, or one hour.  So, the result is a first run time of 1:00am tomorrow.  If by "every day at one o'clock" you meant 1:00pm instead of 1:00am, just use "+13/24" instead of "+1/24".

The last parameter sets the interval between jobs, but I think there is an error in it.  I would set that to the same as the first run time (but this last parameter must be a varchar2 value, not a date, so you need the single quotes):
'trunc(sysdate +1)+1/24'

You could simply use:
'sysdate +1'
which means: "the same time tomorrow".  But if you use this interval for dbms_job, the jobs will tend to drift a bit later every day.  Also, if the database is ever down at the scheduled run time, and is started later, and jobs that were scheduled to run during the down time will then be scheduled for the next day at the same time they actually ran, not at their intended run times. So, an interval value like: 'trunc(sysdate +1)+1/24' is safer, since it will always mean: "tomorrow at 1:00am".
0
 
LVL 1

Author Comment

by:xLeon
ID: 13918059
Thanks, now this makes sense.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question