Thread: Status ofTrigger Firing Order and 'FOR EACH STATEMENT'?

Hi, I am running into a problem with changes in INSERT not showing up in 
outer level triggers (that call the INSERT, which has its own trigger).  I found two archived messages that discuss the
exactissue (with AFTER 
 
triggers).  I am using pgsql 7.4.5.  Given that its been ~4 years since 
the messages below were exchanged, I am wondering if there has been a 
fix or workaround created, or if there is any on the horizon?  Messages:

http://archives.postgresql.org/pgsql-general/2000-11/msg00954.php
http://archives.postgresql.org/pgsql-general/2000-11/msg00981.php

Another thing that would really help performance is NEWSET and OLDSET in 
'FOR EACH STATEMENT' triggers.  Without this, the FOR EACH STATEMENT 
triggers do not meet my needs and it is necessary to do lots of 
duplicate work using FOR EACH ROW.  Has there been any work in this 
area?   Thread:

http://dbforums.com/t579529.html

I did not see either of these issues on the TODO list.  I am not 
familiar with pgsql internals, but I would be willing to work on them if 
a) the scope of required changes is deemed to be small and b) someone 
can point me in the right direction.

regards,

Dan Libby

Dan Libby <dan@libby.com> writes:
> Hi, I am running into a problem with changes in INSERT not showing up in 
> outer level triggers (that call the INSERT, which has its own trigger). 

I think 8.0 will behave the way you want, but with so little detail it's
hard to be sure.
        regards, tom lane

Dan Libby <dan@libby.com> writes:
> I am still curious to hear an estimate of the difficulty of adding 
> rowset vars representing OLDSET and NEWSET to each trigger defined as 
> 'FOR EACH STATEMENT'.

It's on the TODO list, but I don't think anyone has a clear fix on the
implementation effort or performance implications.
        regards, tom lane

Tom Lane wrote:

>Dan Libby <dan@libby.com> writes:
>  
>
>>Hi, I am running into a problem with changes in INSERT not showing up in 
>>outer level triggers (that call the INSERT, which has its own trigger). 
>>    
>>
>
>I think 8.0 will behave the way you want, but with so little detail it's
>hard to be sure.
>  
>
Yes, I saw that this was implemented in the 8.0 beta3 changelog shortly 
after posting the message.  Very Nice!  I intend to install 8.0 and test 
this very soon.  And incidentally I was able to workaround the issue in 
7.4.5 by using BEFORE triggers and setting relevant foreign keys to 
deferred.

I am still curious to hear an estimate of the difficulty of adding 
rowset vars representing OLDSET and NEWSET to each trigger defined as 
'FOR EACH STATEMENT'.  SQL Server (and other DB?) triggers work this way 
and it seems to me that it is much more efficient as your trigger just 
gets called once but you can still manipulate all of the rows that were 
inserted/updated/deleted.  For example, insert a subset of fields into a 
summary/audit/revision/whatever table using just one statement.

I would be willing to attempt/assist with this effort if the path to 
doing it is deemed straightforward. Otherwise, I humbly request the 
feature for consideration in the TODO list. ( I am an able C coder, but 
as-of-yet unfamiliar with postgres internals. )
From MSDN: 
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

deleted and inserted are logical (conceptual) tables. They are 
structurally similar to the table on which the trigger is defined, that 
is, the table on which the user action is attempted, and hold the old 
values or new values of the rows that may be changed by the user action.

An example usage:

-- Audit OLD record.
INSERT INTO
auditEmployeeData(audit_log_type,audit_emp_id,audit_emp_bankAccountNumber,audit_emp_salary,audit_emp_SSN)SELECT'OLD',
del.emp_id,  del.emp_bankAccountNumber,   del.emp_salary,   del.emp_SSNFROM deleted del
 

-- Audit NEW record.
INSERT INTO
auditEmployeeData(audit_log_type,audit_emp_id,audit_emp_bankAccountNumber,audit_emp_salary,audit_emp_SSN)SELECT'NEW',
ins.emp_id,  ins.emp_bankAccountNumber,   ins.emp_salary,   ins.emp_SSNFROM inserted ins
 


Regards,

Dan Libby