触发器如何写？

zhujiaozi 2018-09-28 09:45:30

在一个表上添加触发器，如果ID是空的则不插入数据，将数据插入另一个表；不为空，则插入本表。如何写？

...全文

336 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

卖水果的net 2018-09-28

打赏
举报



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 dropped

SQL>