Skip to toolbar

Forum

Create a PL/SQL Pro...
 
Notifications
Clear all

Create a PL/SQL Procedure to insert employee details into Employee table.  


Rahul Updhyay
Posts: 17
(@rahul)
Active Member
Joined: 2 months ago

Create a PL/SQL Procedure to insert employee details into Employee table. Before inserting, check whether the employee age is eligible or not. Employee age should be 18 or greater. Values are passed as argument to the procedure. If age valid, insert employee record into 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: 3
Admin
(@sql-admin)
Member
Joined: 1 month 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. 

You can read this article.

https://www.sqlqueries.in/what-are-the-types-of-triggers-in-sql-a-complete-guide-updated-2020/

I hope it is useful. 

Reply

Leave a reply

Author Name

Author Email

Title *

Maximum allowed file size is 10MB

 
Preview 0 Revisions Saved
Share: