请帮我看看有没有语法错误，我第一次写这个，但是测试下来是有问题的，自己没办法找到问题在哪儿。
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;
------解决方案--------------------
lz试试看还有其他问题没.
- SQL code
CREATE OR REPLACE TRIGGER tzl060_cpckcx_trigger AFTER UPDATE ON twl090_cpxsckd FOR EACH ROWDECLARE 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;
------解决方案--------------------
1. 正常的触发器不会有事务控制语句，触发器中修改的内容由与外部事务统一处理。
2. loop循环没有退出的语句，估计会死循环。
------解决方案--------------------
表级的触发器中是不能出现commit的，
只有数据库级事务触发器是可以commit的。