ORACLE中用触发器实现两表数据同步[整理]

SQL> CREATE TABLE t1
2 (
3 str VARCHAR2(5)
4 )
5 /

Table created

SQL>
SQL> CREATE TABLE t2
2 (
3 str VARCHAR2(5)
4 )
5 /

Table created

SQL>
SQL> CREATE OR REPLACE TRIGGER tri_test
2 AFTER UPDATE OR INSERT OR DELETE ON t1
3 FOR EACH ROW
4 BEGIN
5 IF deleting THEN
6 DELETE FROM t2 WHERE str=:OLD.str;
7 ELSIF inserting THEN
8 INSERT INTO t2 VALUES(:NEW.str);
9 ELSE
10 UPDATE t2 SET str=:NEW.str WHERE str=:OLD.str;
11 END IF;
12 END;
13 /

Trigger created

SQL> insert into t1 select dbms_random.string('U',2) from dual connect by rownum<3;

2 rows inserted

SQL> commit;

Commit complete

SQL> select * from t1;

STR
-----
SH
MF

SQL> select * from t2;

STR
-----
SH
MF

SQL> update t1 set str='UP' where rownum=1;

1 row updated

SQL> commit;

Commit complete

SQL> select * from t1;

STR
-----
UP
MF

SQL> select * from t2;

STR
-----
UP
MF

SQL> delete from t1 where rownum=1;

1 row deleted

SQL> commit;

Commit complete

SQL> select * from t1;

STR
-----
MF

SQL> select * from t2;

STR
-----
MF

SQL>