Thread: FOR EACH STATEMENT triggers
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-stopperthat 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
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 #
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