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 exact issue (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
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_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
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