For example, I would like to be able to do something like
I appreciate there are potential performance implications if one did something stupid - but that pretty well applies to anything :)
asked 17 Aug '10, 12:23
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.
answered 17 Aug '10, 13:39
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.
answered 17 Aug '10, 13:41