现在的位置: 首页 > 综合 > 正文

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

2012年09月27日 ⁄ 综合 ⁄ 共 1079字 ⁄ 字号 评论关闭

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>

抱歉!评论已关闭.