Write a PL/SQL Trigger to Insert Employee Records Into a Table When Deleted

An HR system has an Employee table that holds a row for each employee within the company. Each record in the table has a employee id, employee name and manager column, that holds the id for the employee's manager. Write a trigger so that when an employee record is deleted, the record details need to be inserted into an table called Employee_archive along with the deleted date.

Table: EMPLOYEE

COLUMN	DATATYPE	CONS.
EMPID	NUMBER	PK
EMP_NAME	VARCHAR2(25)
MANAGERID	NUMBER

Table: EMPLOYEE_ARCHIVE

COLUMN	DATATYPE	CONS.
EMPID	NUMBER	PK
EMP_NAME	VARCHAR2(25)
MANAGERID	NUMBER
DELETED_DATE	DATE

(Hint: Data is case sensitive. Use '/' to terminate the PLSQL block)

Step-by-Step Guide to Creating the Trigger

Introduction to Triggers:
First, understand that a trigger is a special kind of stored procedure that automatically executes when a specific event occurs in the database. In this guide, we’ll focus on creating a trigger that helps archive employee records automatically when they are deleted from the main table.

Choosing the Event:
In this case, we want the trigger to execute before an employee record is deleted from the Employee table. This allows us to capture and save the record in an archive table before it’s removed.

Writing the Trigger Code:
Let's break down the trigger code step by step.

Trigger Declaration:
We start by declaring the trigger with a name. Here, it's called archive:

CREATE TRIGGER archive

This line tells the database we're creating a new trigger called archive.

Specifying the Timing and Event:
Next, we specify when this trigger should run and on which table. We want it to run before a row is deleted from the Employee table:

BEFORE DELETE ON Employee

This means the trigger will activate right before a deletion occurs.

Defining the Scope:
We need the trigger to execute for each row that's deleted, so we include:

FOR EACH ROW

This ensures the trigger processes each row individually.

Writing the Trigger Body:
Now, we define what the trigger should do. In this case, we want to insert the deleted employee record into an archive table:

BEGIN
    INSERT INTO Employee_archive VALUES(:old.EMPID, :old.EMPNAME, :old.MANAGERID, SYSDATE);
END;
/
          

BEGIN and END are used to enclose the actions the trigger will perform.
The INSERT INTO statement copies data from the deleted record (referenced by :old) into the `Employee_archive` table, along with the current date and time (using SYSDATE).

Conclusion:
With this trigger in place, every time an employee record is deleted from the Employee table, it is automatically archived in the `Employee_archive` table. This ensures you have a backup of important data before it gets permanently removed.