CREATE TABLE parent (
        order_num   INT,
        batch       INT,
        chkno       VARCHAR,
        total       INT
) WITHOUT OIDS;
CREATE INDEX batch_chkno ON parent (batch, chkno);

CREATE TABLE child (
        recid       INT,
        order_num   INT,
        apply_amt   INT
) WITHOUT OIDS;

CREATE TABLE totals (
        batch       INT,
        chkno       VARCHAR,
        amount      INT,
        UNIQUE (batch, chkno)
) WITHOUT OIDS;


CREATE OR REPLACE FUNCTION parent_func () RETURNS TRIGGER AS '
DECLARE
BEGIN
    IF TG_OP = ''UPDATE'' THEN
        RAISE NOTICE ''%: batch: %  chkno: %'', TG_NAME, new.batch,
            new.chkno;
    END IF;

    RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER parent_preupd BEFORE INSERT OR UPDATE ON parent
    FOR EACH ROW EXECUTE PROCEDURE parent_func ();

CREATE OR REPLACE FUNCTION child_func () RETURNS TRIGGER AS '
DECLARE
    parentRec       RECORD;                 -- parent record buffer
BEGIN
    IF TG_OP = ''UPDATE'' THEN
        /* find the parent record. */
        SELECT INTO parentRec batch, chkno FROM parent
            WHERE order_num = new.order_num;

        RAISE NOTICE ''%: parentRec.batch: %  parentRec.chkno: %'',
            TG_NAME, parentRec.batch, parentRec.chkno;

        /* update the parent */
        UPDATE parent SET total = total - old.apply_amt
            WHERE order_num = new.order_num;
        /* update totals */
        UPDATE totals SET amount = amount - old.apply_amt
            WHERE batch = parentRec.batch
            AND chkno = parentRec.chkno;
    END IF;

    RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER child_write AFTER INSERT OR UPDATE ON child
    FOR EACH ROW EXECUTE PROCEDURE child_func ();

CREATE OR REPLACE FUNCTION myfunc (integer) RETURNS void AS '
DECLARE
    trip        ALIAS FOR $1;               -- order_num
    childRec    RECORD;
BEGIN
    FOR childRec IN SELECT * FROM child
        WHERE order_num = trip
    LOOP
        UPDATE child SET apply_amt = 0
            WHERE recid = childRec.recid;
    END LOOP;

    UPDATE parent SET batch = NULL, chkno = NULL
        WHERE order_num = trip;

    RETURN;
END;
' LANGUAGE plpgsql;

/* Insert data. */
INSERT INTO totals (batch, chkno, amount) VALUES (100, 'BR-549', 1500);
INSERT INTO parent (order_num, batch, chkno, total) VALUES (99, 100,
        'BR-549', 500);
INSERT INTO child (recid, order_num, apply_amt) VALUES (1, 99, 100);
INSERT INTO child (recid, order_num, apply_amt) VALUES (2, 99, 100);
INSERT INTO child (recid, order_num, apply_amt) VALUES (3, 99, 100);
INSERT INTO child (recid, order_num, apply_amt) VALUES (4, 99, 100);
INSERT INTO child (recid, order_num, apply_amt) VALUES (5, 99, 100);