oracle触发器(当插入或删除或修改一张表A时,能把新数据同步到另一张结构相同的表B里面)

时间:2022-10-16 00:47:55

create or replace trigger testT
  AFTER  INSERT OR UPDATE OR DELETE ON A
  for each row
declare
  -- local variables here
begin
  IF INSERTING THEN
     INSERT INTO b(a,b) VALUES(:NEW.a,:NEW.b);
  ELSIF DELETING THEN
    DELETE FROM b WHERE a=:OLD.a;
  ELSE
     UPDATE b SET b=:NEW.b WHERE a=:OLD.a;
  END IF;
end testT;

SQL> desc a
Name Type         Nullable Default Comments
---- ------------ -------- ------- --------
A    VARCHAR2(20) Y                        
B    VARCHAR2(20) Y                        

SQL> desc b;
Name Type         Nullable Default Comments
---- ------------ -------- ------- --------
A    VARCHAR2(20) Y                        
B    VARCHAR2(20) Y                        


SQL> insert into a values('a','b');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from b;

A                    B
-------------------- --------------------
a                    b

SQL> update a set b='c' where a='a';

1 row updated

SQL> commit;

Commit complete

SQL> select * from b;

A                    B
-------------------- --------------------
a                    c

SQL> delete from a where a='a';

1 row deleted

SQL> commit;

Commit complete

SQL> select * from b;

A                    B
-------------------- --------------------

SQL>