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>
ORACLE中用触发器实现两表数据同步[整理]
最新推荐文章于 2023-04-16 00:10:25 发布