PL/SQL TRIGGER for updating

Question

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.

A materialised view would seem to be a better choice for this. — David Aldridge
– David Aldridge, Commented May 17, 2013 at 6:42

Glenn · Accepted Answer · 2013-05-17 01:47:50Z

5

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;

answered May 17, 2013 at 1:47

Glenn

9,2102 gold badges44 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

davmos · Accepted Answer · 2013-05-17 07:25:39Z

3

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.

edited May 17, 2013 at 7:25

answered May 16, 2013 at 23:05

davmos

9,6074 gold badges45 silver badges46 bronze badges

Collectives™ on Stack Overflow

PL/SQL TRIGGER for updating

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related