Thread: 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
cutcorners, or if         there's some legitimate reason not to allow them. AFAICT SQL         200x doesn't specify
thatthey *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
STATEMENTis 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