create table t_xl
(
xlid varchar2(10) primary key,
xlname varchar2(36) not null,
xlgtzs number(6,0)
);create table t_gt
(
gtid varchar2(20) primary key,
gtname varchar2(50) not null,
gtbh varchar2(10) ,
xlid varchar2(10)
)每一个xlid对应多个gtid,t_xl表中的xlgtzs字段记录该xlid对应的gtid的总数
例如:某个xlid 为 ‘xxx’
则xlgtzs的值为select count(*) from t_gt where xlid = 'xxx'
现在需要创建一个触发器,当insert、delete、update t_gt 表中记录时,则更新t_xl表中的xlgtzs字段;我创建了一个触发器:
create or replace trigger t_xlgtzs
before insert or update or delete
on t_gt
for each row
declare
lineid varchar(10);
gtjs number(6,0);begin
if inserting or updating then
select xlid , count(*) into lineid, gtjs
from table t_gt
where xlid = :new.xlid
group by xlid;
elsif deleting then
select xlid , count(*) into lineid, gtjs
from table t_gt
where xlid = :old.locline
group by xlid;
end if;update t_xl
set xlgtzs = gtjs
where xlid = lineid;
end;在执行insert、delete、update t_gt 表时,由于触发器t_xlgtzs将t_gt表锁定了,所以操作总是失败。
希望各位大侠指点小弟一二,谢谢!
(
xlid varchar2(10) primary key,
xlname varchar2(36) not null,
xlgtzs number(6,0)
);create table t_gt
(
gtid varchar2(20) primary key,
gtname varchar2(50) not null,
gtbh varchar2(10) ,
xlid varchar2(10)
)每一个xlid对应多个gtid,t_xl表中的xlgtzs字段记录该xlid对应的gtid的总数
例如:某个xlid 为 ‘xxx’
则xlgtzs的值为select count(*) from t_gt where xlid = 'xxx'
现在需要创建一个触发器,当insert、delete、update t_gt 表中记录时,则更新t_xl表中的xlgtzs字段;我创建了一个触发器:
create or replace trigger t_xlgtzs
before insert or update or delete
on t_gt
for each row
declare
lineid varchar(10);
gtjs number(6,0);begin
if inserting or updating then
select xlid , count(*) into lineid, gtjs
from table t_gt
where xlid = :new.xlid
group by xlid;
elsif deleting then
select xlid , count(*) into lineid, gtjs
from table t_gt
where xlid = :old.locline
group by xlid;
end if;update t_xl
set xlgtzs = gtjs
where xlid = lineid;
end;在执行insert、delete、update t_gt 表时,由于触发器t_xlgtzs将t_gt表锁定了,所以操作总是失败。
希望各位大侠指点小弟一二,谢谢!
if insert then t_xl.xlgtzs = t_xl.xlgtzs +1 where xlid=:new.xlid;
if update then 不管?
if delete then t_xl.xlgtzs = t_xl.xlgtzs -1 where xlid=:new.xlid;
pragma autonomous_transaction;在你的update语句后面加个 commit;create or replace trigger t_xlgtzs
before insert or update or delete
on t_gt
for each row
declare
pragma autonomous_transaction;
lineid varchar(10);
gtjs number(6,0); begin
if inserting or updating then
select xlid , count(*) into lineid, gtjs
from table t_gt
where xlid = :new.xlid
group by xlid;
elsif deleting then
select xlid , count(*) into lineid, gtjs
from table t_gt
where xlid = :old.locline
group by xlid;
end if; update t_xl
set xlgtzs = gtjs
where xlid = lineid;
commit;
end;
那
if update then
if :new.xlid = :old.xlid then
不管
else
update t_xl.xlgtzs = t_xl.xlgtzs +1 where xlid=:new.xlid;
if sql%notfound then
insert .... t_xl (..xlgtzs .) values (..1.)..
end if;
t_xl.xlgtzs = t_xl.xlgtzs -1 where xlid=:old.xlid;
此处count(*)有问题的,因为本身是对t_gt操作,又要对t_gt进行统计,建议还是在程序中处理,或者单独写一个存储过程调用处理。
暂且不说锁定表的事情你每次都在操作之前(before)进行select count(*) into ...gtjs
完了你又使用update把这个gtjs更新到t_xl表中
那么岂不是相当于没有变化?
before insert or update or delete on t_gt
for each row
declare
lineid varchar(10);
gtjs number(6,0);
begin
select xlid, count(*) into lineid, gtjs
from table t_gt;
if inserting then
update t_xl set gtjs = :gtjs + 1 where xlid = :lineid;
elsif updating then
if xlid <> :new.xlid then
update t_xl set gtjs = gtjs + 1 where xlid = :lineid;
update t_xl set gtjs = :gtjs - 1 where xlid = :old.xlid;
end if;
else
update t_xl set gtjs = :gtjs - 1 where xlid = :lineid;
group by xlid;
end if;
end;