Ok, find attached a script called test.sql that will create three tables
called parent, child, and totals. It will create a simple AFTER UPDATE
trigger on child and a BEFORE trigger on parent simply to show that the
values of batch and chkno are set to NULL right in the beginning. Just load
the thing in with the \i command. There is a function created called
myfunc(int). Simply do: SELECT myfunc(99); to see what happens. After you
execute the function, you will find that parent.total is zero,
child.apply_amt for each record is zero, but totals is still set to 1500. It
should be 1000.
Version info:
rnd=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)
Any insight is appreciated.
TIA
On Tuesday 21 March 2006 09:11 am, Terry Lee Tucker saith:
> Thanks for the response. I'm working on a simplified example now. It will
> take a little time to set it up. I will post all the code for creating the
> much simplified tables and trigger.
>
> Again, thanks for the response...
>
> On Tuesday 21 March 2006 08:15 am, Martijn van Oosterhout saith:
> > On Tue, Mar 21, 2006 at 06:56:23AM -0500, Terry Lee Tucker wrote:
> > > Hello List:
> > >
> > > I posted a question over the weekend regarding this issue but I failed
> > > to communicate effectively what the problem is. I was weary :o[ Let me
> > > try again.
> >
> > I think one of the reasons why you're not getting any responses is that
> > the problem is complicated but you have not provided a complete
> > example. You don't for example say how the trigger is defined
> > (BEFORE|AFTER) (ROW|STATEMENT) DEFERRED yes/no? If you really want an
> > answer you're going to need to provide a complete example people can
> > run on their own systems.
> >
> > have a nice day,
>
> --
> Quote: 39
> "Posterity -- you will never know how much it has cost my generation
> to preserve your freedom. I hope you will make good use of it."
>
> --John Quincy Adams
>
> Work: 1-336-372-6812
> Cell: 1-336-363-4719
> email: terry@xxxxxxxx
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
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);
