Checking if beneficiary is adult (more than 18 year) when inserting

Question

In "beneficiary" table I have "birthDate" column, when beneficiary registered, i want check, if it is not adult (no more than 18 year), I want disallow register

For example today is "2017-04-05" and if beneficiary tries insert birth date "1999-04-06", I want disallow insert. if birth date is "1999-04-05" or earlier, insert must happen.

After searched found trigger are for this? I tried several ways but not realized how to add this restriction to my table.

possible duplicate http://stackoverflow.com/questions/3015431/oracle-age-calculation-from-date-of-birth-and-today — Jpsh, Apr 05 '17 at 18:11
Please post your attempts to help people to improve your code — Aleksej, Apr 05 '17 at 18:14

score 3 · Accepted Answer · answered Apr 05 '17 at 18:20

You can use this:

(If you never update birthDate column, then you can remove this piece: OR UPDATE and recreate trigger )

CREATE TRIGGER trg
BEFORE INSERT OR UPDATE ON beneficiary
FOR EACH ROW
BEGIN
    IF :NEW.birthDate >  add_months(sysdate , -12*18)  then
       RAISE_APPLICATION_ERROR(-20001, 'Underage user');
    END IF;
END;

Stephan · Answer 2 · 2017-04-05T18:18:37.337

0

In SQL Server databases, you can use the DATEDIFF function in your trigger:

DATEDIFF(YEAR, birthDate, SYSDATE) >= 18

In Oracle databases, you can use the MONTHS_BETWEEN function:

months_between(SYSDATE, birthDate)/12 >= 18

edited Apr 05 '17 at 18:18

answered Apr 05 '17 at 18:15

Stephan

666
8
23

whoops no its a SQL Server function. there's a similar approach in oracle, give me a second to find it and ill add it to my answer – Stephan Apr 05 '17 at 18:19

Checking if beneficiary is adult (more than 18 year) when inserting

2 Answers2