Select trigger seems somewhat poorly documented.
First a preliminary observation:
SELECT trigger is not applicable with FOR EACH ROW.
The other alternative is not named so it's current name is ""
I would propose to name it for clarity the same way it is named in Postgres
namely "FOR EACH STATEMENT" and make that as the default so that ""
would resolve to "FOR EACH STATEMENT". This would be backward compatible
but make it clearer for the user when the trigger is fired.
SELECT trigger can only be defined as BEFORE.
But according to my observations it seems that it is rather fired
DURING the select statement is run.
Further it seems to me that during a single select statement it might
be fired multiple times.
Eg.
SELECT * FROM T1 UNION SELECT * FROM T1
A single select trigger on T1 is fired twice.
The same applies to statement
SELECT * FROM T1 WHERE FALSE UNION SELECT * FROM T1 WHERE FALSE
So it does not matter whether anything is actually selected from the
table or not.
The criteria seems to be that the trigger is fired as many times as the
table is mentioned
in the statement.
But if the optimizer eliminates the lookup in a complex query the
trigger is not fired.
This can be proved by running statement
SELECT * FROM (SELECT * T1 WHERE FALSE UNION SELECT * FROM T1 WHERE
FALSE) WHERE FALSE
In this case the trigger is not fired.
So to sum it up it seems that:
"SELECT trigger is fired whenever the dbms makes a lookup on a table
while executing the SELECT statement."
If the above sentence is correct it means that H2 makes 2 lookups while
calculating statement
SELECT * FROM T1 WHERE FALSE UNION SELECT * FROM T1 WHERE FALSE
although no lookup is necessary in this case (but that is just a
suggestion for optimizer improvement not a bug).
My final question is that what use cases do people have for using the
select trigger?
The only one I can come up with is to do some logging/statistics.
The documentation says:"A|BEFORE SELECT|trigger can be used to update a
table on demand"
But I can't figure out what that could mean.
Maybe keeping LAST_ACCESSED column up-to-date?
Anyway I think that my previous questions seem valid in all of these cases.
- Rami Ojares
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
