X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9D9A2329CDB for ; Sat, 18 Sep 2004 17:39:30 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61399-09 for ; Sat, 18 Sep 2004 16:39:26 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5CF51329E4D for ; Sat, 18 Sep 2004 17:39:25 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i8IGdMeZ004961; Sat, 18 Sep 2004 12:39:22 -0400 (EDT) To: "Omkar Rath" Cc: pgsql-sql@postgresql.org Subject: Re: Sequence of SQL command execution involving triggers and stored procedures. In-reply-to: References: Comments: In-reply-to "Omkar Rath" message dated "Fri, 17 Sep 2004 23:54:24 -0500" Date: Sat, 18 Sep 2004 12:39:21 -0400 Message-ID: <4960.1095525561@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/122 X-Sequence-Number: 18968 "Omkar Rath" writes: > Say there is a stored procedure i.e FUNCTION sp_foo(...) which has say, > SQL commands (any of SELECT,INSERT,UPDATE,DELETE) in its body, say S1 > followed by S2 then S3.... Furthermore, execution of statement S2 > results in trigger functions getting invoked (that contain yet other SQL > statements). > > By the time, the trigger functions execute (as a result of S2), S3 has > already executed. In existing PG releases, AFTER triggers are always fired at the completion of the whole interactive command (ie, the outer SELECT that called sp_foo). There are changes in 8.0 to fire them at the end of the specific query that triggered them, which I believe will do what you want. regards, tom lane