create or replace trigger addtree
after insert on orginfo
for each row
declare
cnt number;
cnt2 number;
cnt3 number;
begin
if inserting then
if :new.parentorgid is not null then
select count(*)
into cnt
from orgtree
where orgid = :new.parentorgid
and topid = '2';
if cnt > 0 then
insert into orgtree
(id,
orgid,
orgcode,
parentorgid,
parentorgcode,
orgtype,
orgname,
orgaliasname,
parentid,
mappid,
topid,
orgdegree,
sourceorgtype,
jgsx)
(select orgtree_seq.nextval,
:new.orgid,
:new.orgcode,
:new.parentorgid,
orgcode,
:new.orgtype,
:new.orgname,
:new.orgname,
id,
:new.orgysjgid,
'2',
:new.orgdegree,
'4',
:new.jgsx
from orgtree
where orgid = :new.parentorgid
and topid = '2');
end if;
else
insert into orgtree
(id,
orgid,
orgcode,
parentorgid,
parentorgcode,
orgtype,
orgname,
orgaliasname,
parentid,
mappid,
topid,
orgdegree,
sourceorgtype,
jgsx)
(select orgtree_seq.nextval,
:new.orgid,
:new.orgcode,
'',
'',
:new.orgtype,
:new.orgname,
:new.orgname,
'',
:new.orgysjgid,
'2',
:new.orgdegree,
'4',
:new.jgsx
from orgtree
where orgid = :new.parentorgid
and topid = '2');
end if;
end if; if not (:new.orgtype = '3' or :new.jgsx = '2' and :new.orgdegree <> '4') then
select count(*)
into cnt2
from orginfo a, orginfo b, orgtree c
where a.parentorgid = b.orgid
and b.ysjgbh = c.orgcode
and a.orgid = :new.orgid
and c.topid = '1';
if cnt2 > 0 then
insert into orgtree
(id,
orgid,
orgcode,
parentorgid,
parentorgcode,
orgtype,
orgname,
orgaliasname,
parentid,
mappid,
topid,
orgdegree,
sourceorgtype,
jgsx)
(select orgtree_seq.nextval,
:new.orgid,
:new.orgcode,
c.orgid,
c.orgcode,
:new.orgtype,
:new.orgname,
:new.orgname,
c.id,
:new.orgysjgid,
'1',
:new.orgdegree,
'4',
:new.jgsx
from orginfo a, orginfo b, orgtree c
where a.parentorgid = b.orgid
and b.ysjgbh = c.orgcode
and a.orgid = :new.orgid
and c.topid = '1');
else
if :new.orgdegree <> '1' then
select count(*)
into cnt3
from orgtree
where parentorgid is null
and topid = '1';
if cnt3 > 0 then
insert into orgtree
(id,
orgid,
orgcode,
parentorgid,
parentorgcode,
orgtype,
orgname,
orgaliasname,
parentid,
mappid,
topid,
orgdegree,
sourceorgtype,
jgsx)
(select orgtree_seq.nextval,
:new.orgid,
:new.orgcode,
orgid,
orgcode,
:new.orgtype,
:new.orgname,
:new.orgname,
id,
:new.orgysjgid,
'1',
:new.orgdegree,
'4',
:new.jgsx
from orgtree
where parentorgid is null
and topid = '1');
else
insert into orgtree
(id,
orgid,
orgcode,
parentorgid,
parentorgcode,
orgtype,
orgname,
orgaliasname,
parentid,
mappid,
topid,
orgdegree,
sourceorgtype,
jgsx)
(select orgtree_seq.nextval,
:new.orgid,
:new.orgcode,
'',
'',
:new.orgtype,
:new.orgname,
:new.orgname,
'',
:new.orgysjgid,
'2',
:new.orgdegree,
'4',
:new.jgsx
from orgtree
where orgid = :new.parentorgid
and topid = '2');
end if;
end if;
end if;
end if;
end addtree;
第一次写这个触发器,
运行到这里时就跳出了
select count(*)
into cnt2
from orginfo a, orginfo b, orgtree c
where a.parentorgid = b.orgid
and b.ysjgbh = c.orgcode
and a.orgid = :new.orgid
and c.topid = '1';
而且测试的脚本还卡在那了,这是咋回事呢?
另外有个疑问,在触发器里insert完一个记录后,再调用:new,:new里的属性会不会发生改变呢?
这个变异表的问题是不是可以通过 包 或者AutoCommit方法解决呢?
在摸索中,哪个比较适用呢?
pragma autonomous_transaction;
这个就是自主事务吧?
这样是不是 先insert表orginfo,提交后
再触发这个触发器?
只有成功insert表orginfo才触发。
如果在触发器中写入数据出错的话,就跟前面insert表orginfo没关系,
是不是在数据完整性上有问题?