| timer [message #20226] |
Wed, 08 May 2002 02:10  |
Amy
Messages: 29
Registered: September 1999
|
Junior Member |
|
|
Hello,
I cannot find anything in the OTN website on timer in trigger or procedures (the timer I am refering to is NOT the one in Oracle Forms/Graphics)
Basically, I am having a problem to write a trigger to do the following and i wish somebody can give some idea on how to go about writing the trigger.
### The Scenario ###
When Event1 occured/inserted into TableA, wait for 5 minutes to see if Event2 is also occuring in TableA.
If, Event2 occurs on the 5th minutes in TableA, trigger to insert Event1+Event2 to TableB
But if Event2 DOES NOT occur on the 5th minute, just trigger to insert Event1 to TableB.
### My question is: ###
Since there is no timer function in oracle trigger or oracle stored procedure... which function should I use?
I really hope somebody could share his/her idea with me.
Your help/idea is greatly appreciated.
Many thanks
regards;
amy
|
|
|
|
| Re: timer [message #20235 is a reply to message #20226] |
Wed, 08 May 2002 08:10  |
Christopher Beattie
Messages: 5
Registered: January 2001
|
Junior Member |
|
|
Before I get to the question of the timer function, there are some significant reasons why your approach is simply not feasible in Oracle as stated, and these problems are more important than the ability to sleep the trigger for five minutes.
In the first place the trigger needs to reference the table that the trigger is triggered on. This is known as the mutating table problem, and can be solved, but with difficulty.
In the second place the trigger is a function that is in effect called as a result of the SQL process that triggered it. That process cannot complete until the trigger completes. In effect, you have put a five minute wait on every insert. If the same process inserts events A and B, the B events will merely be queued up because of the trigger. Even with seperate processes you have to deal with locking and commit problems.
Looking at your code, I would suggest that you check for event 2 and insert it in the table only if event 1 had occured less than five minutes ago, but always insert event 1 into the table.
If you need to use a timing pause, you can always program a timing loop into the trigger looking at SYSDATE until five minutes have passed. This is, a CPU intensive loop, however.
|
|
|
|
| Re: timer [message #20292 is a reply to message #20226] |
Sat, 11 May 2002 08:22 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
I absolutely agree with Christopher's remarks, as if the query is done per your idea, it not only slows down the process, it may give a way to deadlock between two inserts, which is worse in a long processes. If you still need to do so with time interval 5 minutes, I suggest you to insert all the rows first in TableB with timestamp (recording the time the row is created/inserted) and check later to filter all the rows in TableB for which were created within 5 min interval and store the records you want in first table TableA. Hope it works. Check it out.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
