Triggers for Multiple Operations

The following steps are required to create a trigger that can be used to perform multiple
operations:

1. Enter the following statements in SQL*Plus to create the tables Employee and Employee_Audit.


• CREATE TABLE Employee
  (
  ID VARCHAR2(4) NOT NULL,
  First_Name VARCHAR2(10),
  Last_Name VARCHAR2(10),
  Start_Date DATE,
  End_Date DATE,
  Salary NUMERIC(8,2),
  city VARCHAR2(10),
  Description VARCHAR2(10)
  );


• 
  CREATE TABLE Employee_Audit
  (
  ID VARCHAR2(4) NOT NULL,
  First_Name VARCHAR2(10),
  Last_Name VARCHAR2(10),
  Salary NUMERIC(8,2),
  Description VARCHAR2(10),
  V_User VARCHAR2(20),
  Updated_Date DATE,
  Inserted_Date DATE,
  Deleted_Date DATE
  );
2. Open an editor (Notepad), enter the following code in it, and then save the file at C:\oracle_11g\c08_oracle_11g with the file name Emp_Audit_Trig.txt. Alternatively, enter the following code directly in SQL*Plus:



• CREATE OR REPLACE TRIGGER Emp_Trig
  BEFORE UPDATE OR DELETE OR INSERT
  ON EMPLOYEE
  FOR EACH ROW
  DECLARE
  V_User VARCHAR2(20);
  N_Date DATE;
  BEGIN
  SELECT USER, SYSDATE INTO V_User, N_Date FROM DUAL;
  IF INSERTING THEN
  IF :NEW.Description = ‘Programmer’ THEN
  IF :NEW.Salary >= 2000 AND :NEW.Salary <= 3000 THEN
  
• --inserting new row into the Employee_Audit table
  
• INSERT INTO EMPLOYEE_Audit(ID, First_Name, Last_Name, Salary, Description, V_User, Inserted_Date) VALUES(:NEW.ID, :NEW.First_Name,:NEW.Last_Name, :NEW.Salary,:NEW.Description, V_User,N_Date);
  
• ELSE
  
• RAISE_APPLICATION_ERROR(-20102, ‘Salary cannot be more than 3000 or less than 2000 for Programmer’);
  
• END IF;
  
• ELSIF :NEW.Description = ‘Tester’ THEN
  
• IF :NEW.Salary >= 3000 AND :NEW.Salary <= 5000 THEN
  
• --inserting new row into the Employee_Audit table
  
• INSERT INTO EMPLOYEE_Audit(ID, First_Name, Last_Name,Salary, Description, V_User, Inserted_Date) VALUES (:NEW.ID, :NEW.First_Name, :NEW.Last_Name,:NEW.Salary,:NEW.Description, V_User,N_Date);
  
• ELSE
  
• RAISE_APPLICATION_ERROR(-20102, ‘Salary cannot be more than 5000 or less than 3000 for Tester’);
  
• END IF;
  
• ELSIF :NEW.Description = ‘Manager’ THEN
  
• IF :NEW.Salary >= 5000 AND :NEW.Salary <= 8000 THEN
  
• --inserting new row into the Employee_Audit table
  
• INSERT INTO EMPLOYEE_Audit(ID, First_Name, Last_Name,Salary, Description, V_User, Inserted_Date) VALUES (:NEW.ID, :NEW.First_Name, :NEW.Last_Name,:NEW.Salary,:NEW.Description, V_User,N_Date);
  
• ELSE
  
• RAISE_APPLICATION_ERROR(-20102, ‘Salary cannot be more than 8000 or less than 5000 for Manager’); END IF;
  
• END IF;
  
• ELSIF UPDATING THEN
  
• --inserting old row into the Employee_Audit table which is going to updated
  
• INSERT INTO EMPLOYEE_Audit (ID, First_Name, Last_Name,Salary, Description, V_User, Updated_Date) VALUES (:OLD.ID, :OLD.First_Name, :OLD.Last_Name,:OLD.Salary,:OLD.Description, V_User,N_Date);
  
• ELSIF DELETING THEN
  
• --inserting deleting row into the Employee_Audit table
  
• INSERT INTO EMPLOYEE_Audit (ID, First_Name, Last_Name,Salary, Description, V_User, Updated_Date) VALUES (:OLD.ID, :OLD.First_Name, :OLD.Last_Name,:OLD.Salary,:OLD.Description, V_User,N_Date);
  
• END IF;
  
• END; /
  

In the given code, the trigger Emp_Trig is created for performing multiple operations such as updating, inserting, or deleting a record from the Employee table. It means the trigger Emp_Trig will be fired when the UPDATE, INSERT, or DELETE statement is issued against the Employee table. This trigger is created such that whenever a user deletes, updates, or inserts data in the Employee table, the information such as employee id, name, salary, description, deleted date, insert date, update date, and user name will be stored in the Employee_Audit table.

In the DECLARE section, V_User and N_Date are declared as the VARCHAR and DATE data types, respectively, to store the user name and the current date. In the BEGIN section, the SELECT statement assigns values to the V_User and N_Date variables from the DUAL table. The statement IF INSERTING THEN evaluates to TRUE, if you issue the INSERT command against the Employee table. Similarly, the ELSIF UPDATING THEN and ELSIF DELETING THEN statements evaluate to TRUE, if you issue the UPDATE and DELETE commands against the Employee table.

As discussed earlier, the old and new column values can be accessed in the row level triggers with the correlation names: OLD and NEW.


3. Now, execute the contents of file to create the trigger Emp_Trig. To execute the contents of the file, enter the following command in SQL *Plus.

SQL> @ ‘C:\Oracle\Emp_Audit_Trig.txt’

The trigger will be created and stored in the database, and can now be executed. Once you create the trigger, it gets associated with the table on which it is created. This trigger will be fired when the UPDATE, INSERT, or DELETE statement is issued on the table, on which the trigger is created.


4. Now, insert a new row into the Employee table on which the trigger Emp_Trig is created. Enter the INSERT statement in SQL*Plus.
   

After inserting the data row in the Employee table, check the data of the Employee_Audit table. You will find that the same row is inserted into the Employee_Audit table.

Again, insert the data row into the Employee table with salary greater than 3000 and description as Programmer. While executing the INSERT statement, Oracle will throw an error with the message Salary cannot be more than 3000 or less than 2000 for Programmer. This is because, as defined in the trigger, the salary of an employee with description as programmer should not be greater than 3000 and not less than 2000.

In the same way, using triggers you can delete and update some records from the Employee table and then check their status in the Employee_Audit table. The records that are deleted as well as the rows that are updated in the Employee table will be stored in the Employee_Audit table.