I feel like you could probably do this in a Rube Goldberg sort of fashion by having custom alerts that trigger when a database changes and triggers an extract refresh through tabcmd. Perhaps Matt Coles has an idea.
I'm not exactly sure I understand the set of needs that got you to the state where you needed this feature, Benjamin. Here's my series of questions as I read through your problem:
1. Are you using 8.0? 9.0 (and now, 9.1) has a lot of features that make working with Custom SQL way easier--namely, the ability to have multiple SQL queries as separate "tables" within the Tableau connection screen. I'd definitely advise upgrading if you're able.
2. Why is Custom SQL necessary? If possible, avoid it wherever you can--it will introduce extreme inefficiencies into your workbook, and is generally more difficult to maintain.
3. What does having the parameter injected into the SQL accomplish for you that a Quickfilter wouldn't? Performance?
4. Whatever the Parameter is set to in your workbook when you publish it, will be used in the extract generation query. So when the extract refreshes, you will be stuck with the data that the parameter told it to get. Someone who is simply viewing one of the Views from the workbook on Tableau can change the parameter, but I believe that's just for their own session, and doesn't actually change the default value for it in the workbook itself--so I suspect you'll get the same data no matter what a Viewer sets the parameter to, unless they Web Edit or publish a new version of the workbook. Again, if you have an extract, why not extract all the data, then let the viewer pick a quickfilter value to filter it?
All that now said, if there really is no better solution, yes, you could write a custom script to query the Tableau Server repository and identify some event there that would then trigger an extract refresh. But Diego is right in calling that a Rube Goldberg machine. I'm confident there are better solutions for you than that route!