From: Craig Servin <cservin@cromagnon.com>
Organization: Cro-Magnon
To: pgsql-sql@postgresql.org
Subject: Statement Triggers and access to other rows in the transaction
Date: Wed, 1 Nov 2006 10:50:33 -0600
User-Agent: KMail/1.9.4
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200611011050.33562.cservin@cromagnon.com>

I have tables representing a ledger system and the accounts on which it 
operates.

I want to do 2 things to all transactions altering the ledger tables.

First, all of the inserts into the ledger tables should be balanced ( They 
should sum to 0 ).  If not I want to abort the transaction.

Second, if the transaction is balanced I want to update the account balance on 
the account tables.


In other database systems that I have used when you have a statement level 
trigger you get access to the inserted/updated/deleted rows from that 
transaction as "virtual" tables within the trigger that you can manipulate.

I am currently using plpgsql to write my triggers.  I have not been able to 
figure out the "right" way to do this in PostgreSQL and would appreciate any 
advice.


Thanks,

Craig