SQL> SQL> create table t1(id int, name varchar(20)); Table created SQL> create table t2(name varchar(20)); Table created SQL> -- 使用表级触发器 SQL> create trigger tri_t1_ins 2 after insert on t1 3 begin 4 -- 把没有 ID 的行,写入到 t2 5 insert into t2(name) select name from t1 where id is null; 6 -- 再删除他们 7 delete t1 where id is null; 8 end; 9 / Trigger created SQL> -- 写入 4 行数据 SQL> begin 2 insert into t1(id, name) 3 select 1000, 'xxxx' from dual; 4 5 insert into t1(id, name) 6 select null, 'yyyy' from dual 7 union all 8 select null, 'zzzz' from dual 9 union all 10 select 2000, 'aaaa' from dual; 11 end; 12 / PL/SQL procedure successfully completed SQL> -- 查看一下结果 SQL> col id format 999999; SQL> col name format a20; SQL> select * from t1; ID NAME ------ -------------------- 1000 xxxx 2000 aaaa SQL> select * from t2; NAME -------------------- yyyy zzzz SQL> drop table t1 purge; Table dropped SQL> drop table t2 purge; Table droppedSQL>
SQL> create table t1(id int, name varchar(20));
Table created
SQL> create table t2(name varchar(20));
Table created
SQL> -- 使用表级触发器
SQL> create trigger tri_t1_ins
2 after insert on t1
3 begin
4 -- 把没有 ID 的行,写入到 t2
5 insert into t2(name) select name from t1 where id is null;
6 -- 再删除他们
7 delete t1 where id is null;
8 end;
9 /
Trigger created
SQL> -- 写入 4 行数据
SQL> begin
2 insert into t1(id, name)
3 select 1000, 'xxxx' from dual;
4
5 insert into t1(id, name)
6 select null, 'yyyy' from dual
7 union all
8 select null, 'zzzz' from dual
9 union all
10 select 2000, 'aaaa' from dual;
11 end;
12 /
PL/SQL procedure successfully completed
SQL> -- 查看一下结果
SQL> col id format 999999;
SQL> col name format a20;
SQL> select * from t1;
ID NAME
------ --------------------
1000 xxxx
2000 aaaa
SQL> select * from t2;
NAME
--------------------
yyyy
zzzz
SQL> drop table t1 purge;
Table dropped
SQL> drop table t2 purge;
Table droppedSQL>