Geek Answers Handbook

Calculate birth age using placq oracle trigger and insert age into table

I have a table

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

I insert the date of birth, and the trigger will calculate the age and insert this 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 only for one line, and I need a trigger for all lines, but if I call it from a trigger, an error occurs.

 CREATE OR REPLACE TRIGGER agec after INSERT OR UPDATE ON dates FOR EACH ROW BEGIN getage; END; /
+2
source share
3 answers

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

No no. I'm not sure that you will pay attention; and there is no reason why you should :-) but:

Do not store age in your database. You are absolutely sure that sometimes you are mistaken. Age changes every year for every person, but it changes every day for some people. This, in turn, means that you need to perform a batch job every day and update the age. If this fails or is not extremely strict and runs twice, you have problems.

You should always calculate the age when you need it. This is a fairly simple request and will save you a lot of pain in the long run.

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

I tweaked SQL Fiddle a bit to demonstrate

Now to answer your question

this procedure works fine, but only for one line, but for all lines I need a trigger, but if I call it from a trigger, then an error occurs ...

You didn’t mention the error, please do it in the future as it is very useful, but I suspect you are getting

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

This is because your procedure requests an updated table. Oracle does not allow this to support consistent reading of data. The way to avoid this is to not query the table you don't need to do. Change your procedure to a function that returns the correct result with 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;

Please note again that I use the months_between() function, since not all years have 365 days.

In your trigger, you assign a 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;

Syntax :new.<column> is a link to an updated <column> . In this case :new.age is the actual value that will be placed in the table.

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

As you can see, the function is completely absent; your trigger may 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 intend to use this function elsewhere in the database, save it separately. It is good practice to save code that is used in many places in such a function so that it is always used the same way. It also ensures that whenever someone calculates age, they will do it correctly.

As a little aside, are you sure you want to let people be 9999 years old? Or 0.000000000001998 (proof) ? Numerical accuracy is based on the number of significant digits; this (according to Oracle) is non-zero numbers. You can easily be caught by this. The database point should limit the possible input values ​​to only those that are valid. I would seriously consider declaring your age column as number(3,0) to include only the “possible” values.

+7
source

If you do this with a trigger, the age will be potentially incorrect in one day. This is why storing age in a database table is bad practice and nobody does it. You need a view.

 create view person_age as select person_id, floor(months_between(trunc(sysdate),birthdate)/12) as age from birthdays;

Take a look at SQL Fiddle

+3
source

If you want AGE to be accessible from the database, there are two options:

  • Define a view that contains age calculation, or
  • Define a virtual column that does the same

To determine this view:

 CREATE OR REPLACE VIEW DATES_VIEW AS SELECT DOB, FLOOR(MONTHS_BETWEEN(SYSDATE, DOB) / 12) AS AGE FROM DATES

The problem is that you have to remember what you selected from DATES_VIEW, but you need to update the DATES, which is mentally disordered. IMO adding a virtual column to the table is cleaner:

 CREATE TABLE DATES (DOB DATE, AGE GENERATED ALWAYS AS (FLOOR(MONTHS_BETWEEN(SYSDATE, DOB) / 12)) VIRTUAL);

Please note that virtual columns cannot be updated.

Any method helps ensure that the AGE is always consistent.

Share and enjoy.

0
source

More articles:


All Articles