Home » SQL & PL/SQL » SQL & PL/SQL » Updating status
Updating status [message #394617] Sat, 28 March 2009 11:38 Go to next message
Mozart
Messages: 7
Registered: March 2009
Location: Dubai
Junior Member

Hello,

I have a job table that can have many tasks, in one to many relationship with the tasks table.

What I would like to do is create a trigger that will update the job status once all the tasks assigned to the job are finished.

Any help will be much appreciated !

Thanks
Re: Updating status [message #394620 is a reply to message #394617] Sat, 28 March 2009 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>What I would like to do is create a trigger that will update the job status once all the tasks assigned to the job are finished.
Based upon which metric & what value can an outside observer conclude "all the tasks assigned to the job are finished."?

Did you read & follow the bouncing RED ball advice as stated in URL below?
http://www.orafaq.com/forum/f/1/136107/
Did you read & follow Posting Guidelines as contained in URL below?
http://www.orafaq.com/forum/t/88153/0/
Re: Updating status [message #394628 is a reply to message #394620] Sat, 28 March 2009 20:00 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Once you have complied with BlankSwan's notices, there are serveral ways to at least attempt what you want:

1) use table level triggers
2) use instead of triggers
3) use fancy materialized view process and constraints?
4) think about making your "TASK ASSOCIATIONS" a nested table column on your jobs table


So why have I mentioned all this stuff? Well... There are several issues with the request you have made. One of the more interesting issues is:

how will your trigger code behave in a multi-user environment?

Most people write trigger code that looks good, and works when tested in single user mode. But it has loopholes in a multi-user environment where several people might be completing or adding tasks to your job at the same time. How do you get a correct count of the remaining tasks on a job in this situation. Indeed, what is the definition of a correct count in this situation?

It may become necessary for you to figure out some kind of locking strategy when you try to do your update of job status. Then you have to figure out how to get everyone who might want to update the job status, to use your locking strategy.

I am sure I am making a mess of this, but... just think about how one process inserting/updating/deleting tasks on your job can cause a problem with a second process trying to count the number of remaining tasks for the job. You must keep transaction semantics and Oracle concurrency design in mind if you want to fully appreciate how this problem is interesting.

It is because of this issue (and a few others (think mutating/constraining table error)), that the four suggestions above have been provided. They are all ways of deailing with the various bumps you will be confronted with then you try to do create the kind of logic you want.

Good luck, Kevin
Re: Updating status [message #394767 is a reply to message #394620] Mon, 30 March 2009 04:24 Go to previous message
Mozart
Messages: 7
Registered: March 2009
Location: Dubai
Junior Member
BlackSwan wrote on Sat, 28 March 2009 21:05
>
Based upon which metric & what value can an outside observer conclude "all the tasks assigned to the job are finished."?


Did you read & follow the bouncing RED ball advice as stated in URL below?
http://www.orafaq.com/forum/f/1/136107/
Did you read & follow Posting Guidelines as contained in URL below?
http://www.orafaq.com/forum/t/88153/0/



Am am not exactly sure what do you mean.



Job_status & Task_status are Varchar2(30) and have the following values ('PENDING','IN PROGRESS', 'COMPLETED')



Sorry, I was in a rush ill read it now >.<

@ Kevin Meade, I am doing this for a school project therefore a code for single-user environment should be sufficient but thanks for the head up I will keep that in mind.

[Updated on: Mon, 30 March 2009 04:36]

Report message to a moderator

Previous Topic: boolean value to string
Next Topic: Without temp table senario
Goto Forum:
  


Current Time: Tue Dec 06 11:55:16 CST 2016

Total time taken to generate the page: 0.21357 seconds