如何使用触发器校验新入职员工年龄是否满18周岁？

原代码问题分析 #

1. 表结构 #

CREATE TABLE STAFF (
Staff_ID NUMBER(5),
First_Name VARCHAR2(25) NOT NULL,
Last_Name VARCHAR2(25),
Date_Of_Birth DATE NOT NULL,
Gender VARCHAR2(25),
Email VARCHAR2(25),
Telephone NUMBER(15),
Department VARCHAR2(25),
Staff_Type VARCHAR2(25),
Occupation VARCHAR2(25),
Hire_Date DATE,
Salary NUMBER (10)
);

2. 原插入语句问题 #

原插入语句多了一个额外值（末尾的1），与表结构的12个字段不匹配，执行会报错，修正后的插入语句应为：

INSERT INTO STAFF VALUES (1,'Ukeme', 'Mfon', TO_DATE('1980-12-2', 'yyyy-mm-dd'), 'Male', 'mfonclals@gmail.com',7826768732, 'Medical Team','Full-time','Nurse', TO_DATE('2023-02-12', 'yyyy-mm-dd'), 40000);

3. 原触发器的致命问题 #

create or replace trigger dob_check
before insert on Staff
for each row
declare
minimum_age number := 18;
begin
if extract(year from (sysdate - :old.Date_Of_Birth) year to month) < minimum_age
then
raise_application_error (-20000, 'Minimum age is '||minimum_age);
end if;
end;
/

• 错误1：引用:old变量：INSERT触发器中，:old代表旧数据，但插入操作还未生成任何旧记录，因此:old.Date_Of_Birth会触发编译错误，必须改为:new.Date_Of_Birth（代表即将插入的新数据）。
• 错误2：年龄计算逻辑不严谨：仅通过年份差判断年龄，会忽略月份和日期的差异。例如当前日期是2024-05-20，员工出生日期为2006-06-01，年份差为18，但实际员工还未到18周岁生日，此时触发器会错误允许插入。

修正后的触发器代码 #

create or replace trigger dob_check
before insert on Staff
for each row
declare
    minimum_age number := 18;
begin
    -- 准确判断：出生日期加上18年后的日期是否小于等于当前日期
    if add_months(:new.Date_Of_Birth, minimum_age * 12) > sysdate then
        raise_application_error (-20000, '员工必须年满' || minimum_age || '周岁');
    end if;
end;
/

验证效果 #

• 测试未满18岁的插入：

  INSERT INTO STAFF VALUES (2,'Test', 'User', TO_DATE('2007-01-01', 'yyyy-mm-dd'), 'Male', 'test@example.com',1234567890, 'Admin','Full-time','Clerk', SYSDATE, 30000);
  

  执行后会触发错误：ORA-20000: 员工必须年满18周岁，符合预期。
• 测试满18岁的插入：
  使用修正后的合法插入语句（如原插入语句去掉多余值），触发器不会报错，数据正常插入。

内容的提问来源于stack exchange，提问作者Solomon