sql - Calculating age from birthday with oracle plsql trigger and insert the age in table

Question

i have a table

dates
(dob date,
age number(4)
);

I will insert a date of birth and a trigger will calculate the age and insert that age in the age field.

CREATE OR REPLACE PROCEDURE getage IS 
ndob date;
nage number(10);
BEGIN
select dob into ndob from dates; 
select (sysdate-to_date(ndob))/365 into nage from dual;
update dates set age=nage;
END;
/
SHOW ERRORS;

this procedure works fine but for only one row, and I need trigger for all the rows but if I call it from a trigger then the error occurs.

CREATE OR REPLACE TRIGGER agec after INSERT OR UPDATE ON dates
FOR EACH ROW
BEGIN
getage;
END; 
/

Answer 1

please help...i really need this...

No, you don't. I'm not sure you'll pay attention; and there's no reason why you should :-) but:

Do not store age in your database. You are absolutely guaranteed to be wrong occasionally. Age changes each year for each person, however, it changes every day for some people. This in turn means you need a batch job to run every day and update age. If this fails, or isn't extremely strict and gets run twice, you're in trouble.

You should always calculate the age when you need it. It's a fairly simple query and saves you a lot of pain in the longer run.

select floor(months_between(sysdate,<dob>)/12) from dual

I've set up a little SQL Fiddle to demonstrate

---

Now, to actually answer your question

this procedure works fine but for only one row,,,but for all the rows i need trigger but if i call it from a trigger then the error occurs...

You don't mention the error, please do this in future as it's very helpful, but I suspect you're getting

ORA-04091: table string.string is mutating, trigger/function may not see it

This is because your procedure is querying the table that is being updated. Oracle does not allow this in order to maintain a read-consistent view of the data. The way to avoid this is to not query the table, which you don't need to do. Change your procedure to a function that returns the correct result given a date of birth:

function get_age (pDOB date) return number is
   /* Return the the number of full years between 
      the date given and sysdate.
      */    
begin    
   return floor(months_between(sysdate,pDOB)/12);    
end;

Notice once again that I'm using the months_between() function as not all years have 365 days.

In your trigger you then assign the value directly to the column.

CREATE OR REPLACE TRIGGER agec before INSERT OR UPDATE ON dates
FOR EACH ROW
BEGIN
   :new.age := get_age(:new.dob);
END;

The :new.<column> syntax is a reference to the <column> that is being updated. In this case :new.age is the actual value that is going to be put in the table.

This means that your table will automatically be updated, which is the point of a DML trigger.

As you can see there's little point to the function at all; your trigger can become

CREATE OR REPLACE TRIGGER agec before INSERT OR UPDATE ON dates
FOR EACH ROW
BEGIN
   :new.age := floor(months_between(sysdate,:new,DOB)/12);
END; 

However, having said that, if you are going to use this function elsewhere in the database then keep it separate. It's good practice to keep code that is used in multiple places in a function like this so it is always used in the same way. It also ensures that whenever anyone calculates age they'll do it properly.

As a little aside are you sure you want to allow people to be 9,999 years old? Or 0.000000000001998 (proof)? Numeric precision is based on the number of significant digits; this (according to Oracle) is non-zero numbers only. You can easily be caught out by this. The point of a database is to restrict the possible input values to only those that are valid. I'd seriously consider declaring your age column as number(3,0) to ensure that only "possible" values are included.