CREATE OR REPLACE TRIGGER TRIG_SUM_tParent_tChild AFTER INSERT OR UPDATE OR DELETE ON tChild FOR EACH ROW declare i number; BEGIN if deleting or updating then if :old.child_value is not null then UPDATE tParent SET tParent.sum_child = tParent.sum_child-:OLD.child_value, tParent.count_child = tParent.count_child-1 WHERE tParent.parent_id = :new.parent_id; end if; end if; if inserting or updating then if :new.child_value is not null then UPDATE tParent SET tParent.sum_child = NVL(tParent.sum_child,0)+:NEW.child_value, tParent.count_child = NVL(tParent.count_child,0)+1 WHERE tParent.parent_id = :new.parent_id; end if; end if; END; /
有一点小问题,改过就可以了。 CREATE OR REPLACE TRIGGER TRIG_SUM_tParent_tChild AFTER INSERT OR UPDATE OR DELETE ON tChild FOR EACH ROW declare i number; BEGIN if deleting or updating then if :old.child_value is not null then UPDATE tParent SET tParent.sum_child = tParent.sum_child - :OLD.child_value, tParent.count_child = tParent.count_child-1 WHERE tParent.parent_id = :OLD.parent_id; end if; end if; if inserting or updating then if :new.child_value is not null then UPDATE tParent SET tParent.sum_child = NVL(tParent.sum_child,0) + :NEW.child_value, tParent.count_child = NVL(tParent.count_child,0)+1 WHERE tParent.parent_id = :new.parent_id; end if; end if; END;
AFTER INSERT
OR UPDATE
OR DELETE
ON tChild
FOR EACH ROW
declare
i number;
BEGIN
if deleting or updating then
if :old.child_value is not null then
UPDATE tParent
SET tParent.sum_child = tParent.sum_child-:OLD.child_value,
tParent.count_child = tParent.count_child-1
WHERE tParent.parent_id = :new.parent_id;
end if;
end if;
if inserting or updating then
if :new.child_value is not null then
UPDATE tParent
SET tParent.sum_child = NVL(tParent.sum_child,0)+:NEW.child_value,
tParent.count_child = NVL(tParent.count_child,0)+1
WHERE tParent.parent_id = :new.parent_id;
end if;
end if;
END;
/
CREATE OR REPLACE TRIGGER TRIG_SUM_tParent_tChild
AFTER INSERT
OR UPDATE
OR DELETE
ON tChild
FOR EACH ROW
declare
i number;
BEGIN
if deleting or updating then
if :old.child_value is not null then
UPDATE tParent
SET tParent.sum_child = tParent.sum_child - :OLD.child_value,
tParent.count_child = tParent.count_child-1
WHERE tParent.parent_id = :OLD.parent_id;
end if;
end if;
if inserting or updating then
if :new.child_value is not null then
UPDATE tParent
SET tParent.sum_child = NVL(tParent.sum_child,0) + :NEW.child_value,
tParent.count_child = NVL(tParent.count_child,0)+1
WHERE tParent.parent_id = :new.parent_id;
end if;
end if;
END;