On Nov 14, Neil wrote:

 >>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

It appears that statement triggers are implemented this way right now in 
CVS tip. Unfortunately, this means that there's no way to figure out the 
record set that is covered by the triggering action, which I consider 
being fundamental for a usable implementation. There should be new 
references,
called OLDSET and NEWSET or something like that.


 >> - 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...


MS SQL2000 has only AFTER STATEMENT triggers too, which seems to be 
sufficient.

I had a look at the trigger calling code in execMain.c. For BEFORE ROW 
triggers, a compare of rows before and after trigger execution is 
performed, row by row. Figuring out the difference could be difficult 
for statement triggers, so it might be reasonable to disallow BEFORE 
STATEMENT triggers. Additional effect is that the OLDSET and NEWSET 
record sets visible to the trigger code only need to be read-only, which 
solves the problem deciding whether an update to NEWSET will itself fire 
triggers or not.

Is there any more work on statement triggers planned or going on right now?

Regards,

Andreas


Tue, 29 Apr 2003 10:19:40 +0200
Andreas Pflug <Andreas.Pflug@web.de>
FOR EACH STATEMENT triggers
Andreas Pflug wrote:
> 
> On Nov 14, Neil wrote:
> 
>  >>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
> 
> It appears that statement triggers are implemented this way right now in
> CVS tip. Unfortunately, this means that there's no way to figure out the
> record set that is covered by the triggering action, which I consider
> being fundamental for a usable implementation. There should be new
> references,
> called OLDSET and NEWSET or something like that.

Agreed.

> 
>  >> - 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...
> 
> MS SQL2000 has only AFTER STATEMENT triggers too, which seems to be
> sufficient.

Since BEFORE ROW triggers can modify or suppress the resulting changes,
there is no way to have a BEFORE STATEMENT trigger if we also want
NEWSET and OLDSET.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Tue, 29 Apr 2003 15:30:18 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: FOR EACH STATEMENT triggers
Andreas Pflug said:
> It appears that statement triggers are implemented this way right now in
> CVS tip. Unfortunately, this means that there's no way to figure out the
> record set that is covered by the triggering action, which I consider
> being fundamental for a usable implementation.

I agree that this would be useful -- I didn't get around to implementing
it when I did the rest of the statement-trigger work.

> Is there any more work on statement triggers planned or going on right
> now?

I don't have any work on them planned. If you or anyone else would like to
take a shot at implementing this functionality, go right ahead. Otherwise,
I might get a chance to do some work on this in August or September.

Cheers,

Neil


Wed, 30 Apr 2003 02:56:47 -0400 (EDT)
"Neil Conway" <neilc@samurai.com>
Re: FOR EACH STATEMENT triggers