Forum

Write a PL/SQL Proc...
 
Notifications
Clear all

Write a PL/SQL Procedure to insert employee details into the Employee table. Before inserting, check whether the employee's age is eligible or not.


Rahul Updhyay
Posts: 17
Topic starter
(@rahul)
Active Member
Joined: 1 year ago

Write a PL/SQL Procedure to insert employee details into the Employee table. Before inserting, check whether the employee's age is eligible or not. Employee age should be 18 or greater. Values are passed as an argument to the procedure. If age valid, insert employee record into a table and print the message "Age valid - Record inserted", else print the message "Age invalid - Record not inserted" by raising an exception.

1 Reply
SQL Admin
Posts: 6
Admin
(@sql-admin)
Member
Joined: 1 year ago

PL/SQL Procedure to insert employee details into the Employee table. Before inserting, check whether the employee's age is eligible or not. Employee age should be 18 or greater. Values are passed as an argument to the procedure. If age valid, insert employee record into a table and print the message "Age valid - Record inserted

CREATE OR REPLACE TRIGGER

Trigger_before_employee_ForInsert
BEFORE INSERT
on employee_details
FOR EACH ROW

DECLARE
Employee_age number;

BEGIN

-- Finding employee age by date of birth
SELECT MONTHS_BETWEEN

(TO_DATE(sysdate,'DD-MON-YYYY'),

TO_DATE(:new.DATE_OF_BIRTH,'DD-MON-YYYY'))/12
INTO Employee_Age FROM DUAL;

-- Check whether employee age is greater than 18 or not
IF (Employee_AGE < 18) THEN
RAISE_APPLICATION_ERROR
(-10000,'Age invalid record not inserted

Employee age must be greater than or equal to 18.');
END IF;

Sample triggers related to the above query.

Trigger to insert employee details into another employee table without a condition:

CREATE OR REPLACE PROCEDURE COPY_EMPLOYEENAME()
LANGUAGE SQL
BEGIN
DECLARE V_ENAME VARCHAR(10);
SELECT ENAME INTO V_ENAME FROM EMPLOYEE WHERE EMPLOYEEID='001';
INSERT INTO EMPLOYEE_A VALUES('EMPLOYEE FIRST NAME: ' || V_ENAME);
END;

Trigger to capture employee details "wherever there is an insert "capture the record into the employee audit table". 

CREATE TRIGGER tr_Emplpoyee-ForInsert
on Employee
FOR INSERT
AS
BEGIN
Declare @EmpID int
Select @EmpID = EmpID from inserted

insert into EmployeeAudit
Values('New employee with EmpID =' +
Cast(@EmpID as nvarchar(5)) +
' is added at ' +
cast(Getdate() as nvarchar(20)
)
END

You can also use the same query to capture deleted records into the employee audit table by just replacing the delete in place of the insert

The Update is a bit complicated, but not soo difficult to understand. 

I hope it is useful. 

Reply
Share: