oracle 触发器创建

Question

oracle创建一个触发器，有一张表结构如下

我想求的是如果插入一条新数据，dept_name为南京，不插入dept_code 的情况下，根据dept_name，给他加上320110
用触发器做，每插入一行执行一次
代码如下
create table ceshi (id number,name VARCHAR2(50),dept_name VARCHAR2(50),dept_code VARCHAR2(50))
insert into ceshi values(1,'zhangsan','北京','110000');
insert into ceshi values(2,'zhangsan','上海','310000');
insert into ceshi values(3,'zhangsan','南京','320100');
insert into ceshi values(4,'zhangsan','深圳','440300');
insert into ceshi values(5,'zhangsan','广州','440100');
commit;
select * from ceshi

Answer 1

create or replace trigger TRI_testnotnull
before insert on ceshi
for each row
declare
v_dept varchar2(50);

PRAGMA AUTONOMOUS_TRANSACTION;
begin
select a.dept_code into v_dept from ceshi a where a.dept_name='南京';
if :new.dept_code is null
then
:new.dept_code:=v_dept;

end if;
end TRI_testnotnull;