Calculating Age from Birthday with Oracle Plsql Trigger and Insert the Age in Table

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

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.

calculate age from date of birth and program start date

Use the built in function MONTHS_BETWEEN. As the age is typically counted in years, simple divide by 12 and truncate to the whole number:

trunc(MONTHS_BETWEEN(ch.STARTDATE,pe.DATEOFBIRTH)/12) age

Examples - birthday today

select trunc(MONTHS_BETWEEN(DATE'2021-03-04',DATE'2000-03-04')/12) age  from dual;
       AGE
----------
        21
    

Birthday tomorrow

select trunc(MONTHS_BETWEEN(DATE'2021-03-04',DATE'2000-03-05')/12) age  from dual;        
       AGE
----------
        20

I want to create AFTER UPDATE trigger to update DATE OF BIRTH where (AGE 20)

Let's take a walk down "Revision Lane" as this code can make use of some. But first as work about formatting. You need to learn how to do it. In fact I tell my students that if they submit unformatted code they fail that assignment. I don not even bother to read it. Format your code. I should be able to get an idea of the flow just looking at it. It does not really matter how you format, just as long as it consistent through out. Admittedly it is not a big deal for 10 lines of code, but the time to form good habits is before forming bad ones. Now for what you have:



Be careful setting and depending on setting NLS_DATE_FORMAT for here
it is fine. But what happens when you need to call another routine
an that developer also sets NLS_DATE_FORMAT but different from yours.



Once a column/parameter is a date data type never use the to_date function on it, it is already a date. Oracle provides a slew of date processing functions and makes date arithmetic available. In this case both sysdate and the column DOB are already dates so do not do to_date() on them.

Your expression 

    months_between(to_date(sysdate,'dd-mon-yyyy'),
                   to_date(:new.dob,'dd-mon-yyyy'))/12

Becomes just simply 

    months_between(sysdate, :new.dob)/12

Going a step further. Within a plsql block there is no need to Select ... into unless you are actually retrieving from a table (or view, etc). You can just make a direct assignment to a variable.
So with this and the above:

   select months_between(to_date(sysdate, :new.dob)/12
     into stu_age 
     from dual; 
     
becomes a simple assignment:
    
    stu_age := months_between(to_date(sysdate, :new.dob)/12; 

At this point your trigger has been reduced to:

create or replace trigger trupdt
    after update
       on sycs_dbms
      for each row
declare 
    stu_age number;
begin 
    --to check the age by date of birth
    stu_age := months_between(sysdate,:new.dob)/12; 
end trupdt; 

Unfortunately, it is completely useless. You computed the months between the 2 dates as a numeric value (with decimal places), but then the trigger ends and just throws that calculation away doing nothing with it. I do not think this is what you after. You initially stated you wanted to update dob. Well there are 2 issues here.

1. DOB is defined as a date so your calculation needs to result in a date. This does not.
2. You have a after update trigger but an after update trigger
   cannot change column values, You need a before update trigger.

Now you could the calculated value and a date calculation to get the DOB value, with the appropriate logic to determine if you even should. But the suggestion by @Barmar is extremely good. I'll us it. Thanks Barmer. Instead of the months_between function use the add_months function with sysdate to calculate the date 20 years ago, then use the least function to choose the appropriate value. Also make a direct assignment to :new_dob bypassing the local variable. The final trigger for this becomes simply:

create or replace trigger sycs_dbms_dob_ge20_bur
   before update
       on sycs_dbms
      for each row
begin
    :new.dob := least(:new.dob, add_months(sysdate, -240)); -- (20yrs * 12mon/yr)    
end sycs_dbms_dob_ge20_bur; 

I have put together a fiddle here that walks through each step above. I would not typically provide a complete answer to an obvious homework assignment. But if I had assigned this I would have walked through it in the next class. My hope is you take this to class and discuss it the professor and other students. Do not just submit as your own, any half-way decent professor would catch on real fast. But at least study carefully what it does and how it got there from where you started.

PL/SQL Program to calculate age and whether your birthday year is a leap year or not

You can also try to check it with conversions to Julian dates. When you convert a timestamp to a Julian date you get the number of days since some date before -4000 BC. If you calculate the difference between two dates you know the number of days between those dates. So do it for January 1st and December 31st of you birth year and you'll know if it was leap year or not.

For calculating age check the function months_between (check the last answer for this question).

Writing a Trigger to find and fill the age of a patient whenever a patient record is inserted into patients table

Unless it is a homework question, it really doesn't make much sense as "age" depends on current date.

Anyway, see if this helps.

SQL> create table patient
  2    (id               number primary key,
  3     date_of_birth    date,
  4     age              number
  5    );

Table created.

Trigger: calculate age as months between sysdate and date of birth divided by 12 (as there are 12 months in a year); certainly, this is just an approximate value, but - as far as I understood - your task isn't to correctly calculate age, but to learn how to use triggers.

SQL> create or replace trigger ia_patient
  2    before insert on patient
  3    for each row
  4  begin
  5    :new.age := round(months_between(sysdate, :new.date_of_birth) / 12);
  6  end;
  7  /

Trigger created.

Testing:

SQL> insert into patient (id, date_of_birth) values (2, date '2000-10-05');

1 row created.

SQL> select * from patient;

        ID DATE_OF_BI        AGE
---------- ---------- ----------
         2 2000-10-05         20

SQL>

Oracle PL/SQL, How to calculate age from date birth using simple substitution variable and months_between

All SQL examples from others and me are probably better thing to use unless you are learning something about PL/SQL. Here's what I came up with:

 DECLARE
  v_dob        DATE:= to_date('&v_dob', 'MM/DD/YYYY'); -- copy this line and you'll be fine
  v_your_age   NUMBER(3, 1);
BEGIN
  v_your_age := TRUNC(MONTHS_BETWEEN(SYSDATE, v_dob))/12;  
  DBMS_OUTPUT.PUT_LINE ('Your age is ' || v_your_age);
END;
/

I passed 01/01/2010. Got this in output: 

 Your age is 3.1

Another SQL example:

SELECT empno, ename, hiredate
    , TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) years_of_service  
 FROM scott.emp
/

mysql trigger calculating age by using DOB field and autostore category based on calculated age to Database

As I tried to explain in my comment, you do not want to store something like this in your database. As soon as you do, it becomes a maintenance headache and is almost instantly out of date. For example: a 9-year-old is entered into the DB. Next week is his birthday. He is now 10, yet the database still has an entry of child (0-9) for him. Bad design.

Instead you select what you want correctly:

SELECT TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age,
CASE 
     WHEN TIMESTAMPDIFF(YEAR, dob, CURDATE()) < 10 then "child" 
     WHEN TIMESTAMPDIFF(YEAR, dob, CURDATE()) > 14 then "adult"
     ELSE "young"
END as category
FROM table_name;

Here is a sqlfiddle of it in action: http://sqlfiddle.com/#!9/bbc860/1

---

Related Topics