如何验证员工年龄，即在插入记录之前必须大于18岁(在创建表但不工作时使用检查约束)

Question

--dept table
create table department(
dept_id number(5)  ,
dept_name varchar2(100),
dept_city varchar2(100) ,
dept_country varchar2(100),
CONSTRAINT dept_pk PRIMARY KEY(dept_id)
);

insert into department( dept_id, dept_name, dept_city, dept_country )values(1,'hr','hyderabad','india');
insert into department( dept_id, dept_name, dept_city, dept_country )values(2,'marketing','banglore','india');
insert  into department(dept_id,  dept_name, dept_city, dept_country)values(3,'sales','dhaka','bangladesh');

create sequence s1
start with 1
increment by 1;
create table employee(
employee_id number(10)  ,
employee_name varchar2(100) NOT NULL,
employee_age number(3) ,
employee_sal number(9,2),
dept_id    number(5),
CONSTRAINT employee_pk PRIMARY KEY(employee_id),
constraint dept_fk foreign key(dept_id) references department(dept_id)
);

CREATE OR REPLACE TRIGGER trg_before_emp_insr
BEFORE INSERT
on employee_details
FOR EACH ROW 
DECLARE
emp_age number;
BEGIN
IF (employee_age < 18) THEN
RAISE_APPLICATION_ERROR(-20000,'Employee age must be greater than or equal to 18.');
END IF;
END;
/

insert into employee(employee_id, employee_name,  employee_age, employee_sal,dept_id )values(s1.nextval,'ravi',45,7333,1);
insert into employee(employee_id,  employee_name,  employee_age, employee_sal,dept_id  )values(s1.nextval,'sai',74,4451,2);
insert  into employee(employee_id, employee_name,  employee_age, employee_sal,dept_id )values(s1.nextval,'chandu',35,9428,3);
insert  into employee( employee_id,employee_name,  employee_age, employee_sal,dept_id )values(s1.nextval,'raju',7,25422,2);
insert  into employee( employee_id,employee_name,  employee_age, employee_sal,dept_id )values(s1.nextval,'teja',36,7955,1);
select * from employee

Answer 1

您想使用:NEW记录从插入的行中获取值(并使用EMPLOYEE表而不是EMPLOYEE_DETAILS(：

CREATE OR REPLACE TRIGGER trg_before_emp_insr
BEFORE INSERT
on employee
FOR EACH ROW 
BEGIN
IF (:NEW.employee_age < 18) THEN
RAISE_APPLICATION_ERROR(-20000,'Employee age must be greater than or equal to 18.');
END IF;
END;
/

db<gt；小提琴这里

---

但是，您应该考虑存储出生日期而不是年龄，因为明天(或者肯定是明年(年龄值将过时，但存储出生日期和计算年龄不会。

create table employee(
employee_id   number(10)  ,
employee_name varchar2(100) NOT NULL,
employee_dob  DATE,
employee_sal  number(9,2),
dept_id       number(5),
CONSTRAINT employee_pk PRIMARY KEY(employee_id),
constraint dept_fk foreign key(dept_id) references department(dept_id)
);
CREATE OR REPLACE TRIGGER trg_before_emp_insr
BEFORE INSERT
on employee
FOR EACH ROW 
BEGIN
IF :NEW.employee_dob > TRUNC(ADD_MONTHS(SYSDATE, -18*12)) THEN
RAISE_APPLICATION_ERROR(-20000,'Employee age must be greater than or equal to 18.');
END IF;
END;
/

db<gt；小提琴这里