Quick SQL Trigger Question

gerTheGreat

Just a little question. I have two tables, one contails personal details, (email addresses, date of birth , etc), the other stores the public information, (user name, location, age, etc). I also have a function that will get the user's age from the date of birth. I'm trying to use a trigger to call the function when a row is inserted into the tables, (the tables will be inserted into at the same time). Unfortunately, when I try to insert into the second table, (the one with the datafield age), I get the following errors:

ORA-04091
ORA-06512
ORA-04088

Having done some research on google, all I seam to find are messages saying that I can't modify/query that table until the trigger/function has completed. But isn't that a contradiction?

Can anyone help? I'm really confused.

Seth Brundle

Is it a before or after trigger and on which table?
has that table containing the age been populated at this stage?
Show us some code

gerTheGreat

The two tables will be inserted into at the same time, ( at the time of registration)

table for personal details, (ie for private info):
user_id Number Primary Key,
.
.
.
dob DATE NOT NULL,
.
.
.
etc.

table for user details, (ie public info):
user_name Varchar2(30) Primary Key,
user_password Varchar2(7) NOT NULL,
age NUMBER,
.
.
.
etc.

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;


trigger:

CREATE OR REPLACE TRIGGER AGE_FUNC_TRIGGER
AFTER INSERT OR UPDATE OF AGE ON USER_DETAILS
FOR EACH ROW
DECLARE
AGE_IN USER_DETAILS.AGE%TYPE;
DOB PERSONAL_DETAILS.DOB%TYPE;
BEGIN
AGE_IN := AGE_FUNC(DOB);
UPDATE USER_DETAILS SET AGE = AGE_IN;
END AGE_FUNC_TRIGGER;

I've tried making the trigger shoot, before and after, but neither work.

Seth Brundle

You have a trigger executing on a column just after you changed that column. The column will then change causing the trigger to re-run, etc.
replace the value about to be inserted with the newly computed value

CREATE OR REPLACE TRIGGER AGE_FUNC_TRIGGER
[B]BEFORE [/B] INSERT OR UPDATE OF AGE ON USER_DETAILS
FOR EACH ROW
DECLARE
AGE_IN USER_DETAILS.AGE%TYPE;
DOB PERSONAL_DETAILS.DOB%TYPE;
BEGIN
AGE_IN := AGE_FUNC(DOB);
[B]:new.AGE = AGE_IN;
--U[/B]PDATE USER_DETAILS SET AGE = AGE_IN;
END AGE_FUNC_TRIGGER;

gerTheGreat

thanks

Seth Brundle

no bother.
Incidentally, I don't know if your trigger does other stuff not shown but the line below is redundant here.
DOB PERSONAL_DETAILS.DOB%TYPE;

gerTheGreat

Just gave the code a shot, the only problem is that it's inserting blank values into the in age field. Do I have to read the dob in or will is it called in automatically. IE,

select dob from personal_details into dob;

followed by the line

AGE_IN := AGE_FUNC(DOB);


Edit: It's cool, I found that I needed to change the trigger to this:

CREATE OR REPLACE TRIGGER AGE_FUNC_TRIGGER
BEFORE INSERT OR UPDATE OF AGE ON USER_DETAILS
FOR EACH ROW
DECLARE
AGE_IN USER_DETAILS.AGE%TYPE;
DOB_IN PERSONAL_DETAILS.DOB%TYPE;
U_ID PERSONAL_DETAILS.USER_ID%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;
AGE_IN := AGE_FUNC(DOB_IN);
:new.AGE := AGE_IN;
END AGE_FUNC_TRIGGER;


Thanks for all the help kbannon, very much apreaciated!

Seth Brundle

Yeah - I see all that now. I had missed the fact that DOB wasn't assigned a value earlier.