how to validate employee age that it must me greater than 18 before inserting record(tiried using check constraint while creating table but not work)

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

Solution 1:

You want to use the :NEW record to get the value from the row being inserted (and to use the EMPLOYEE table rather than 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<>fiddle here

However, you should consider storing date of birth rather than age as tomorrow (or definitely next year) the age value will be outdated but storing the date of birth and calculating the age would not.

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