Forum

Forums
SQL
Other Documents
Write a PL/SQL Proc...
 
Share:
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.

 
    Last Post
RSS

Posts: 17
 Rahul Updhyay
Topic starter
Aug 23, 2020 12:59 pm
(@rahul)
Active Member
Joined: 5 years 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.

This topic was modified 4 years ago 2 times by KV Kumar
1 Reply
Posts: 92
 SQL Admin
Admin
Sep 02, 2020 5:17 am
(@sql-admin)
Estimable Member
Joined: 4 years 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. 

This post was modified 4 years ago 4 times by KV Kumar
Reply

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: