Creating trigger for date of Birth

Question

Please can someone enlighten me on how to create trigger for date of birth having upper limit of 60 and lower limit of 20. I am working on an assignment that requires constraint on staff age range for a shipping company on oracle 11g. thank you

It would be nice to see what you've tried and what problems you are having, but it's Friday (thank Crunchy) and I'm feeling generous, so [this answer](http://stackoverflow.com/a/12364681/266304) ought to give you a good starting point. — Alex Poole, Apr 12 '13 at 16:33
You can't implement a meaningful constraint on upper age limit as a trigger on date of birth because the age changes without the data changing. Thus people become 60 years old without an invalidation being raised. In other words, what is being taught here is syntax (which you can get from the manual) rather than any kind of software development methodology. — David Aldridge, Apr 12 '13 at 17:52

score 2 · Answer 1 · answered Apr 12 '13 at 23:30

2

Don't use triggers to enforce relational integrity, use constraints. That's what they're for.

You haven't troubled yourself to provide us with the table structure, so you will need to tweak this for your actual table.

alter table employees
    add constraint minimum_age_ck check
         (hire_date >= add_months(date_of_birth, 240);

alter table employees
    add constraint maximum_age_ck check
         (hire_date <= add_months(date_of_birth, 720);

Of course, if your employees table lacks a hire_date column you got a big problem.

answered Apr 12 '13 at 23:30

APC

144,005
19
170
281

great solution. *don't use triggers* is always the way to go ! – haki Apr 13 '13 at 07:06
@APC Since an employee might have multiple periods of employment with the company, the hire date arguably ought to be in a different table anyway. – David Aldridge Apr 15 '13 at 16:51

user2282435 · Answer 2 · 2013-04-15T13:25:19.107

-1

Thanks. I finally used this

alter table staff
    add constraint minimum_age_ck check
         ((dateOfBirth >= add_months(dateOfBirth, -18)));

and this

alter table staff
    add constraint maximum_age_ck check
         ((dateOfBirth <= add_months(date_of_birth, 60)));

Will try inserting the values and check if the constraints are valid.

Meanwhile a check on the above answer by inserting values proved it was not valid. i also tried using this ALTER TABLE STAFF ADD CONSTRAINT minimum_age_ck CHECK ( (months_between (sysdate, dateOfBirth) >= 240)); CHECK ( (months_between (sysdate, dateOfBirth) >= 240)) * ERROR at line 3: ORA-02436: date or system variable wrongly specified in CHECK constraint

Is sysdate not a valid usable comparison for months_between?

edited Apr 15 '13 at 13:25

answered Apr 15 '13 at 12:08

user2282435

1
1

You can't use sysdate in a check constraint. As I commented on your question, the check constraint is evaluated only when data changes, hence you are not allowed to use sysdate in a constraint because it cannot be guaranteed that all data passes the constraint. In your code, what is "dateOfBirth", if the column is named "date_of_birth"? If they're the same then your first constraint is saying that the dae of birth must be greater than 18 months prior to itself, which it always is, and less than 60 months prior to 60 months after itself, which it also always is. This approach is all wrong. – David Aldridge Apr 15 '13 at 13:32
Thank you David Aldridge. How could it be structured to achieve the said check? – user2282435 Apr 16 '13 at 10:01

Creating trigger for date of Birth

2 Answers2