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