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


14 Nov 2002 21:56:17 -0500
Neil Conway <neilc@samurai.com>
FOR EACH STATEMENT triggers
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


Fri, 15 Nov 2002 11:16:05 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: FOR EACH STATEMENT triggers