Creating a trigger to insert and remove rows

Question

I have been given an assignment to create a trigger that works when tables are inserted or updated or deleted in a table. If it is a delete or update then the table must store the older values before the action on another table. If it is insert then the new row should be added to the new table. Also it should include the number of rows that are affected by each action. So far this is what I have done:

CREATE OR REPLACE TRIGGER archive_update 
BEFORE INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW

BEGIN

INSERT INTO archive_emp(EMP_ID, FIRST_NAME, LAST_NAME, BIRTH_DAY, 
  SEX, SALARY, SUPER_ID, BRANCH_ID)
VALUES(:new.EMP_ID, :new.FIRST_NAME, :new.LAST_NAME,
 :new.BIRTH_DAY, :new.SEX, :new.SALARY, :new.SUPER_ID, :new.BRANCH_ID);

END;

If you found the answer useful, consider accepting it so that it would also help others.Also read this — Kaushik Nayak

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2019-04-23T14:27:55

To check the dml operation you may use an IF condition with appropriate dml predicate

CREATE OR REPLACE TRIGGER archive_update 

BEFORE
     INSERT OR UPDATE ON EMPLOYEE
     FOR EACH ROW
BEGIN

IF UPDATING OR DELETING THEN --You may add this
     INSERT INTO archive_emp(  emp_id, first_name, last_name,
                               birth_day, sex,salary,super_id,branch_id
     ) VALUES (:old.emp_id, :old.first_name,:old.last_name,:old.birth_day,
               :old.sex,:old.salary,:old.super_id,:old.branch_id
     );
ELSIF INSERTING THEN --and this
     INSERT INTO archive_emp(  emp_id, first_name, last_name,
                               birth_day, sex,salary,super_id,branch_id
     ) VALUES (:new.emp_id, :new.first_name,:new.last_name,:new.birth_day,
               :new.sex,:new.salary,:new.super_id,:new.branch_id
     );

END IF;

END;
/

Creating a trigger to insert and remove rows

1 Answers