SELECT SUM(:old.SUB_END*:old.INT_DEB_CRE) INTO oldEND FROM ACC_SUB_COD WHERE INT_REP=(SELECT NUM FROM ACC_REP_COD) GROUP BY INT_REP;//此语句出错:这句是不是就是下面的意思?
oldEND := :old.SUB_END*:old.INT_DEB_CRE;
oldEND := :old.SUB_END*:old.INT_DEB_CRE;
CREATE TRIGGER dbo.acc_sub_cod_upd ON dbo.acc_sub_cod
FOR UPDATE
AS
IF update(sub_end)
BEGIN
UPDATE acc_rep_cod
SET rep_end=rep_end-(SELECT SUM(sub_end*deb_cre) FROM deleted WHERE rep_num=acc_rep_cod.num GROUP BY rep_num)
+(SELECT SUM(sub_end*deb_cre) FROM inserted WHERE rep_num=acc_rep_cod.num GROUP BY rep_num)
WHERE num=(SELECT rep_num FROM inserted)
END
IF update(sub_beg)
BEGIN
UPDATE acc_rep_cod
SET rep_beg=rep_beg-(SELECT SUM(sub_beg*deb_cre) FROM deleted WHERE rep_num=acc_rep_cod.num GROUP BY rep_num)
+(SELECT SUM(sub_beg*deb_cre) FROM inserted WHERE rep_num=acc_rep_cod.num GROUP BY rep_num)
WHERE num=(SELECT rep_num FROM inserted)
UPDATE acc_sub_cod
SET sub_end=sub_end-(SELECT sub_beg FROM deleted)
+(SELECT sub_beg FROM inserted)
WHERE num=(SELECT num FROM inserted)
END
AFTER UPDATE ON acc_sub_cod
FOR EACH ROW
BEGIN
IF :old.sub_end <> :new.sub_end then
UPDATE acc_rep_cod
SET rep_end=rep_end-decode(num,:old.rep_num,:old.sub_end*:old.deb_cre,0)
+ :new.sub_end*:new.deb_cre
WHERE num=:new.rep_num;
END IF;
IF :old.sub_beg <> :new.sub_beg then
UPDATE acc_rep_cod
SET rep_end=rep_end-decode(num,:old.rep_num,:old.sub_beg*:old.deb_cre,0)
+ :new.sub_beg*:new.deb_cre
WHERE num=:new.rep_num;
:new.sub_end=:old.sub_end-:old.sub_beg+:new.sub_beg;
END IF;
end;
/
UPDATE acc_rep_cod
SET rep_end=rep_end-(SELECT SUM(sub_end*deb_cre) FROM deleted WHERE rep_num=acc_rep_cod.num GROUP BY rep_num)//子查询条件rep_num=acc_rep_cod.num
+(SELECT SUM(sub_end*deb_cre) FROM inserted WHERE rep_num=acc_rep_cod.num GROUP BY rep_num)//子查询条件rep_num=acc_rep_cod.num
WHERE num=(SELECT rep_num FROM inserted)num是acc_sub_cod的主键。
+ :new.sub_end*:new.deb_cre你可以试试我写的触发器,使用一条更新很多条记录的SQL语句,应该可以达到你要的效果。
rep_end=rep_end-decode(num,:old.rep_num,:old.sub_end*:old.deb_cre,0)
+ :new.sub_end*:new.deb_cre
能不能写成 rep_end=rep_end-decode(num,:old.rep_num,:old.sub_end*:old.deb_cre,0)
+decode(num,:old.rep_num,:new.sub_end*:new.deb_cre,0)这个也要条件查询的呀,是不是有了上面的“DECODE”下面就不要用了。beckhambobo(beckham):整个触发器代码我写了呀,不过是SQL SERVER 7。0的,不知写成ORACLE 怎么写!
而你的查询条件已经有WHERE num=:new.rep_num;,所以num是肯定等于:new.rep_num的,也就不需要decode了。