Deadlock Issue in the trigger .......please do help [message #364566] |
Tue, 09 December 2008 02:10 |
jaysri
Messages: 2 Registered: December 2008
|
Junior Member |
|
|
Hi all,
I do have four attibutes start_date ,end_date ,fiscal_start_yr,
fiscal_end_yr
start_date,end_date in table1
fiscal_start_yr,fiscal_end_yr in table2.
We do use java base application GUI.
Requirement: When project start and end dates are changed from GUI ,DB trigger has to fire and fiscal years are set accordingly.
and when fiscal year are changed from GUI ,Db trigger has to fire and project dates should set accordingly.
so to achive this i creared the following two triggers.
This causes deadlock as first triggers update action causes second trigger to fire and vice versa.
Please do help if this can be achived in any way.
Thanks in advance.
CREATE OR REPLACE TRIGGER table1_723
AFTER UPDATE OF start_date,finish_date
ON table1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
update table2 a
set fiscal_start_yr = XXX,fiscal_end_yr= XXXX
where a.id = :NEW.id;
END table1_723;
CREATE OR REPLACE TRIGGER table2_723
AFTER UPDATE OF fiscal_start_yr,fiscal_end_yr
ON table2
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
Update table1 a
set finish_date = xxx,a.start_date = xxx
where a.id =:NEW.id;
END table2_723;
|
|
|
|
|
Re: Deadlock Issue in the trigger .......please do help [message #364576 is a reply to message #364566] |
Tue, 09 December 2008 02:42 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It seems unlikely to me that you'd get a deadlock off this, as both triggers will fire from the same session (unless you're doing something stupid with Autonomous Transactions), and your locks don't prevent you from re-altering the changes that you've made.
What is the actual error message that you're getting?
I'd guess it's some sort of maximum recursion level thing, or the session is just hanging as your triggers keep firing each other.
You will need to describe what you think should happen.
Given the logic you have described, an infinite loop is a reasonable outcome.
I suspect that the solution will be an additional check in trigger on Table 1 to check if the new financial year would be different to the current financial year, and to only perform the update if that is the case.
|
|
|
|