CODEWITHSUNDEEP
oracle-databaseplsql
Arunselvan
Arunselvan

Reputation: 75

PL/SQL TRIGGER for updating

I have two tables emp & copy_emp with same structure and same datas. I want if any changes occurred in emp should be reflect in copy_emp. Hence i created a trigger which i gave below.

create or replace trigger t
after
insert or
update of empno or
delete
on emp
for each row
begin
case
when inserting then
  insert into copy_emp(empno,ename,sal) values (:NEW.empno,:NEW.ename,:NEW.sal);
when updating then
  if updating('empno') then
    update copy_emp set copy_emp.empno=:new.empno where copy_emp.empno=:old.empno;
  elsif updating('ename') then
    update copy_emp set copy_emp.ename=:new.ename where copy_emp.ename=:old.ename;
  elsif updating('sal') then
    update copy_emp set copy_emp.sal=:new.sal where copy_emp.sal=:old.sal;
end if;
when deleting then
  delete from copy_emp where empno = :old.empno;
end case;
end;

Trigger got created without any errors and inserting and deleting work fine. problem is with updating. when i update empno column in emp table, it also get updated in copy_emp table. but when i update ename or sal column it only get updated in emp table but not in copy_emp table. Please help.

Upvotes: 1

Views: 7025

Answers (2)

davmos
davmos

Reputation: 9587

The trigger is not firing because you need to include the ename and sal columns in the update of part of the declaration like this...

create or replace trigger t
after
insert or 
update of empno, ename, sal or
delete on emp

However, with your current if/else logic, only one column will ever get updated, even if the update statement contains more than one column - whichever one matches the first if will get updated. You could change the if/else to a series of if, but that would lead to multiple updates running. So, unless you have an unusual requirement, you probably want to go with the solution from @Glenn.

Upvotes: 3

Glenn
Glenn

Reputation: 9170

What if multiple columns are updated at once? You may just want to capture all updates:

CREATE OR REPLACE TRIGGER emp_trigger
  AFTER INSERT OR UPDATE OR DELETE ON emp
  FOR EACH ROW

BEGIN

    IF INSERTING THEN

        INSERT INTO copy_emp(empno, ename, sal)
          VALUES (:NEW.empno, :NEW.ename, :NEW.sal);

    ELSIF UPDATING THEN

        UPDATE copy_emp
          SET copy_emp.empno = :NEW.empno
             ,copy_emp.ename = :NEW.ename
             ,copy_emp.sal   = :NEW.sal
          WHERE copy_emp.empno = :OLD.empno;

    ELSE

        DELETE FROM copy_emp
          WHERE empno = :OLD.empno;

    END IF;

END emp_trigger;

Upvotes: 5

Related Questions