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

Tom Lane wrote:

>Dan Libby <dan(at)libby(dot)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_SSN
FROM 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_SSN
FROM inserted ins

Regards,

Dan Libby