create or replace trigger update_table_sum
after insert on 表1
for each row
begin
update 表2
set LJS=LJS+1
where XMMC='ZPTJ'||to_char(new.ZTPJ)
commit;
end;
/
after insert on 表1
for each row
begin
update 表2
set LJS=LJS+1
where XMMC='ZPTJ'||to_char(new.ZTPJ)
commit;
end;
/
after insert on 表1
for each row
begin
update 表2
set LJS=LJS+1
where XMMC='ZPTJ'||to_char(:new.ZTPJ)
commit;
end;
/
BEFORE DELETE ON TABLE1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF NEW.ZTPJ=1 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZTPJ1;
ELSIF NEW.ZTPJ=2 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZTPJ2;
ELSIF NEW.ZTPJ=3 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZTPJ3;
END IF; --同理,其它相同
IF NEW.ZXZC=1 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZXZC1;
ELSIF NEW.ZXZC=2 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZXZC2;
ELSIF NEW.ZXZC=3 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZXZC3;
END IF; IF NEW.SFXC=1 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=SFXC1;
ELSIF NEW.SFXC=2 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=SFXC2;
ELSIF NEW.SFXC=3 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=SFXC3;
END IF;END;这只是一种简单的方法。当然如果项比较多的话,你也可以把它改写成动态SQL语句。
AFTER INSERT ON TABLE1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.ZTPJ=1 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZTPJ1;
ELSIF :NEW.ZTPJ=2 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZTPJ2;
ELSIF :NEW.ZTPJ=3 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZTPJ3;
END IF; --同理,其它相同
IF :NEW.ZXZC=1 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZXZC1;
ELSIF :NEW.ZXZC=2 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZXZC2;
ELSIF :NEW.ZXZC=3 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=ZXZC3;
END IF; IF :NEW.SFXC=1 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=SFXC1;
ELSIF :NEW.SFXC=2 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=SFXC2;
ELSIF :NEW.SFXC=3 THEN
UPDATE TABLE2 SET LJS=LJS+1 WHERE XMMC=SFXC3;
END IF;END;