Frustrating Problem with an oracle stored procedure

gerTheGreat

This has been bugging for me for ages, I just can't fix it. Can anyone help.

I have two tables:PERSONAL_DETAILS and USER_DETAILS. Personal details is used to store, well personal details, including the user's date of birth. User details is used to store publicly accessable details including age. have created a function to get the age in years by comparing the user's date of birth against the system date. (it's working fine). I then created a trigger to be fired after the insert on the user details table to get call the function and update the user details table by insering the right age.

My problem is that i keep getting three errors, (ORA-00036, ORA-06512, ORA-04088). Can anyone see the error, because I'm stumped and fed-up. thanks ger...

The function:

CREATE OR REPLACE FUNCTION AGE_FUNC (BIRTH_DATE DATE)
RETURN NUMBER
IS AGE NUMBER;
BEGIN
AGE := TRUNC((MONTHS_BETWEEN(SYSDATE, BIRTH_DATE))/12);
RETURN AGE;
END;


The trigger, (and problem):

CREATE OR REPLACE TRIGGER AGE_FUNC_TRIGGER
AFTER INSERT OR UPDATE OF AGE ON USER_DETAILS
DECLARE
AGE_IN USER_DETAILS.AGE%TYPE;
DOB_IN PERSONAL_DETAILS.DOB%TYPE;
U_ID PERSONAL_DETAILS.USER_ID%TYPE;
U_NAME PERSONAL_DETAILS.USER_NAME%TYPE;
BEGIN
SELECT MAX(USER_ID) INTO U_ID FROM PERSONAL_DETAILS;
SELECT DOB INTO DOB_IN FROM PERSONAL_DETAILS WHERE USER_ID = U_ID;
SELECT USER_NAME INTO U_NAME FROM PERSONAL_DETAILS WHERE USER_ID = U_ID;
AGE_IN := AGE_FUNC(DOB_IN);
UPDATE user_details SET AGE = AGE_IN WHERE user_name = U_NAME;
END AGE_FUNC_TRIGGER;

Beano

if you are getting ORA-00036 then your trigger is causing another trigger to be executed which is causing the AGE_FUNC_TRIGGER to be executed again so it just ends up in an infinite loop that blows the stack. Do you any triggers on the user_details table?

Mr. Magoo

OK dont hold me to this but when your trigger runs after the insert/update on USER_DETAILS it updates USER_DETAILS causing the trigger to run again causing an infinite loop. instead of using an update statement in the trigger work out the age and then do old.age := age_in;

Beano

after reading that again Mr Magoo is right. I thought the trigger was on the PERSONAL_DETAILS table. Having an update trigger on a table that updates the table will cause the problem you are having. I would ask though why yo uhave the trigger on the USER_DETAILS table? If you want to update the age on the USER_DETAILS when the DOB on the PERSONAL_DETAILS table changes then should the trigger not be on the PERSONAL_DETAILS table?

gerTheGreat

I've finally got it!!! The recursive loop - it was caused by the update statement. I can't believe that I didn't cop that!! Thanks a million, Beano and Mr Magoo. The frustration finally ends!:D