请帮我看看有没有语法错误,我第一次写这个,但是测试下来是有问题的,自己没办法找到问题在哪儿。
create or replace trigger Tzl060_cpckcx_TRIGGER
after update on twl090_cpxsckd
for each row
DECLARE
v_ckdbh char(11);
v_cplsh number(8);
v_bz char(1);
cursor cur_ckdcx is
select t130.ckdbh,case when count(*)*t015.mbxzs <> (select sum(xssl) from twl130_cpxsckd_mx where ckdbh=t130.ckdbh and cplsh = t130.cplsh) then '1' else '0' end bz,t130.cplsh
--into v_ckdbh,v_bz,v_cplsh
from twl130_cpxsckd_mx t130
join tzl052_ckcptbjl t052 on t052.ckdlsh=t130.ckdlsh
left join tzl003_cpz t003 on t003.cptbbh=t052.cptbbh
left join tzl041_ctbjl t041 on t041.cptbbh_new=t052.cptbbh
join tqj015_cpdmb t015 on t015.cplsh=t130.cplsh
where t130.ckdbh= :OLD.CKDBH
group by t130.ckdbh,t130.cplsh,t015.mbxzs;
BEGIN
IF :NEW.zt = '1' THEN
open cur_ckdcx;
loop
fetch cur_ckdcx into v_ckdbh,v_cplsh,v_bz;
delete from tzl060_cpckcx2
where ckdbh=v_ckdbh and cplsh=v_cplsh;
insert into tzl060_cpckcx2(ckdbh, cplsh, bz)
values(v_ckdbh, v_cplsh, v_bz);
end loop
close cur_ckdcx;
commit;
END IF;
END;
create or replace trigger Tzl060_cpckcx_TRIGGER
after update on twl090_cpxsckd
for each row
DECLARE
v_ckdbh char(11);
v_cplsh number(8);
v_bz char(1);
cursor cur_ckdcx is
select t130.ckdbh,case when count(*)*t015.mbxzs <> (select sum(xssl) from twl130_cpxsckd_mx where ckdbh=t130.ckdbh and cplsh = t130.cplsh) then '1' else '0' end bz,t130.cplsh
--into v_ckdbh,v_bz,v_cplsh
from twl130_cpxsckd_mx t130
join tzl052_ckcptbjl t052 on t052.ckdlsh=t130.ckdlsh
left join tzl003_cpz t003 on t003.cptbbh=t052.cptbbh
left join tzl041_ctbjl t041 on t041.cptbbh_new=t052.cptbbh
join tqj015_cpdmb t015 on t015.cplsh=t130.cplsh
where t130.ckdbh= :OLD.CKDBH
group by t130.ckdbh,t130.cplsh,t015.mbxzs;
BEGIN
IF :NEW.zt = '1' THEN
open cur_ckdcx;
loop
fetch cur_ckdcx into v_ckdbh,v_cplsh,v_bz;
delete from tzl060_cpckcx2
where ckdbh=v_ckdbh and cplsh=v_cplsh;
insert into tzl060_cpckcx2(ckdbh, cplsh, bz)
values(v_ckdbh, v_cplsh, v_bz);
end loop
close cur_ckdcx;
commit;
END IF;
END;
AFTER UPDATE
ON twl090_cpxsckd
FOR EACH ROW
DECLARE
v_ckdbh CHAR (11);
v_cplsh NUMBER (8);
v_bz CHAR (1); CURSOR cur_ckdcx
IS
SELECT t130.ckdbh,
CASE
WHEN COUNT (*) * t015.mbxzs <>
(SELECT SUM (xssl)
FROM twl130_cpxsckd_mx
WHERE ckdbh = t130.ckdbh AND cplsh = t130.cplsh)
THEN '1'
ELSE '0'
END bz,
t130.cplsh
--into v_ckdbh,v_bz,v_cplsh
FROM twl130_cpxsckd_mx t130 JOIN tzl052_ckcptbjl t052
ON t052.ckdlsh = t130.ckdlsh
LEFT JOIN tzl003_cpz t003 ON t003.cptbbh = t052.cptbbh
LEFT JOIN tzl041_ctbjl t041 ON t041.cptbbh_new = t052.cptbbh
JOIN tqj015_cpdmb t015 ON t015.cplsh = t130.cplsh
WHERE t130.ckdbh = :OLD.ckdbh
GROUP BY t130.ckdbh, t130.cplsh, t015.mbxzs;
BEGIN
IF :NEW.zt = '1'
THEN
OPEN cur_ckdcx; LOOP
FETCH cur_ckdcx
INTO v_ckdbh, v_cplsh, v_bz; EXIT WHEN cur_ckdcx%NOTFOUND; --少了退出条件,会死循环 DELETE FROM tzl060_cpckcx2
WHERE ckdbh = v_ckdbh AND cplsh = v_cplsh; INSERT INTO tzl060_cpckcx2
(ckdbh, cplsh, bz
)
VALUES (v_ckdbh, v_cplsh, v_bz
);
END LOOP; CLOSE cur_ckdcx;
-- commit;触发器中不能提交
END IF;
END;
2. loop循环没有退出的语句,估计会死循环。
只有数据库级事务触发器是可以commit的。