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

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