Triggers with FOR EACH STATEMENT

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Perform" <pgsql-performance(at)postgresql(dot)org>
Subject: Triggers with FOR EACH STATEMENT
Date: 2005-04-01 16:20:25
Message-ID: 20050401161901.M13616@narrowpathinc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi All,

I have a trigger function that copies data from an input table to a table in
the actual data model. The data model table has a trigger after update on it.
Is the first trigger fired after the copy terminates or after each insert?
Is the second trigger fired after the first trigger is complete or once for
every iteration of the loop in the first trigger? I only want these triggers
to fire after the previous action is complete. That is what I thought I was
getting when I chose the FOR EACH STATEMENT attribute. Here are excerpts from
the various programs that are running. Your thoughts are appreciated.

From a bash shell COPY is used to put data in the input table.
cat ${v_load_dir}/${v_filename}.ld | \
psql --echo-all \
--dbname ${DB} \
--username dbuser \
--command \
"COPY tbl_status
FROM stdin
WITH DELIMITER AS ','
NULL AS '';"

The input table has an AFTER-INSERT-STATEMENT trigger.
CREATE TRIGGER tgr_xfr_status
AFTER INSERT
ON tbl_status
FOR EACH STATEMENT
EXECUTE PROCEDURE tf_xfr_status();

The input table trigger uses a LOOP to process each newly inserted record.
FOR rcrd_order IN SELECT...
LOOP
-- Now update the information in the detail table.
UPDATE tbl_detail
SET closed = rcrd_order.closed
WHERE tbl_detail.number = rcrd_order.so_number;
END LOOP;

The data model table has an AFTER-UPDATE-STATEMENT trigger.
CREATE TRIGGER tgr_update_allocated
AFTER UPDATE
ON tbl_detail
FOR EACH STATEMENT
EXECUTE PROCEDURE tf_update_allocated();

Kind Regards,
Keith

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2005-04-01 16:36:43 Re: enable_XXX options
Previous Message Peterson, Bjorn 2005-04-01 16:04:08 enable_XXX options