Please add a way of using variables or database values in event trigger conditions

[justin_willey]

For example, I would like to be able to do something like

CREATE EVENT LogNotifier
TYPE LogDiskSpace
WHERE event_condition( 'LogFreePercent' ) < (select DiskThreshold from Settings)
HANDLER
BEGIN
   MESSAGE 'LogNotifier message'
END;

I appreciate there are potential performance implications if one did something stupid - but that pretty well applies to anything 🙂

[Breck_Carter]

That raises the question "how often should SQL Anywhere evaluate the WHERE clause?" Previously it only had to watch the event_condition for any change, but now the event could be fired even if the event_condition remained static. Perhaps a SCHEDULE clause could be used to specify "how often?"

[Former Member · Accepted solution]

The conditions allowed in an event WHERE clause are limited for exactly that reason: performance. The WHERE clause applies only to system events, and these can potentially be triggered very frequently. By limiting what is allowed, the server can evaluate whether or not to fire the event without the overhead incurred by permitting arbitrarily complex expressions, and can do so without creating a temporary connection for the event. If you need to add more complex logic, add an IF statement at the start of the handler with a RETURN statement as its body.

Another alternative (which would be more appropriate for your example above) is to create the event dynamically using EXECUTE IMMEDIATE to incorporate the value from the subquery, and recreate it when the Settings table is changed. This would avoid creating a temporary connection and evaluating the query every time the log file grows.

[justin_willey · Accepted solution]

Thanks Bruce, the dynamic gives me an idea - I put a trigger on the setting that re-creates the events whenever the threshold changes.

[MCMartin]

You could take a differen approach by using the event GrowLog which is fired, if the log file shall be grown. At that point you are able to check the disk space based on a value from a select statement. Or if you have the feeling that this situation might be to late to check, you could create an event which runs every 10 minutes checking the diskspace in question.

[justin_willey]

Thanks Martin - I see there are GrowDB and GrowTemp events as well which would cover the other disk monitoring events I have. However I think the easiest answer is using a trigger as described in my comments on Bruce's answer.