The question is not "how" to make a rewriting but "where" you want rewriting to happen. You said you want something to be like in IIS, but this is not possible in MSSQL. In IIS you have request processing pipeline and there are many points where you can insert your custom code into this pipelene (i.e. create your own ISAPI extention, HTTPModule, events in Global.asax)
In SQL Server there is no SQL pipeline that is opened for developers. When SQL request came to SQL server, you cannot rewrite it. Triggers BEFORE or INSTEAD OF update/insert/delete will not solve the problem if you want to replace the table name in original query because if this table doesn't exists, you will get an exception prior you will be able to execute any custom business logic in triggers.
However you can "rewrite" SQL on the client (not on SQL server) by creating the custom data provider (if you have ADO.NET application). See MSDN - Implementing a .NET Framework Data Provider. This is not an easy task and will require a lot of efforts. In you code you may reuse most standard features of standard MSSQL Data Provider (System.Data.SqlClient) and override only functions where you want to parse SQL and replace certain commands.
Please also look on http://sqlfaultretryprovide.codeplex.com/ - this project was to build a custom .NET data provider to SQL Server that can provide automatically "retry on error" function. So this is not a SQL rewriter, but can be used as an example to build your own.