Create a PL/SQL Procedure to insert employee details into Employee table.
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.
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
FOR EACH ROW
-- 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.');
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()
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);
Trigger to capture employee details "wherever there is an insert "capture the record into the employee audit table".
CREATE TRIGGER tr_Emplpoyee-ForInsert
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)
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.
I hope it is useful.