Calculating age from birthday with oracle plsql trigger and insert the age in table
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; /
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.