如何在插入記錄之前驗證員工年齡必須大于18歲（在創建表時使用檢查約束但不作業）-有解無憂

--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

uj5u.com熱心網友回復：

您想使用: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<>在這里擺弄

但是，您應該考慮存盤出生日期而不是年齡，因為明天（或肯定是明年）年齡值將過時，但存盤出生日期并計算年齡不會。

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<>在這里擺弄

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/415962.html

標籤：

上一篇：Oracle基于日期列的字母順序分配

下一篇：日期-2年