FOR EACH STATEMENT triggers
I'd like to implement FOR EACH STATEMENT triggers. AFAICS it shouldn't
be too tricky -- so if there's some show-stopper that prevented it
from being done earlier, let me know now, please :-)
Some random notes on the implementation I'm thinking of:
- in the function called by a per-statement trigger, no
references to the 'OLD' or 'NEW' rows will be allowed
- should we allow per-statement BEFORE triggers? DB2 doesn't,
but I'm not sure that's because they just cut corners, or if
there's some legitimate reason not to allow them. AFAICT SQL
200x doesn't specify that they *aren't* allowed, so I'm
inclined to allow them...
- if the statement effects zero rows, a per-statement trigger
is still executed
- COPY executes per-statement INSERT triggers, to stay
consistent with the current behavior WRT per-row INSERT
triggers
- specifying 'FOR EACH xxx' in CREATE TRIGGER should now be
optional; if neither is specified, FOR EACH STATEMENT is the
default. This is per SQL spec (SQL 200x, 11.39, 8)
Comments?
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Looks pretty sweet, Neil.
Maybe you could look at column triggers while you're at it, per comment on
Compiere page ;)
Triggers
Compiere uses triggers to ensure data consistency. It seems that in general,
Oracle triggers are relatively easy to convert. In addition to the Function
issues, a procedure needs to be crated per trigger. Oracle and PostgreSQL
have slightly different notation of the "new" and "old" references and
INSERTING, etc. PostgreSQL Triggers do not support Column restrictions
(AFTER UPDATE OF column, column ON table).
- if the statement effects zero rows, a per-statement trigger
is still executed
"affects" - sorry couldn't help myself :)
Chris