While SQL Server 2005/2008 have a DDL Login Trigger Event, they don't have a Logout event. This was brought up on the SQL Forums in a post in the Database Engine Forum, and I found it odd that SQL lacked the opposite event. Oracle has Logout Triggers, so it seemed odd that SQL would be lacking this for comparisons. An interesting solution was posted by Jim McLeod that uses the Service Broker to accomplish the same type of action. He also posted his code on his blog.
I would tend to agree with the comments on the post that the original purpose for this request isn't really a SQL Server issue, but more of an application issue, and delegating the cleanup of a connection to SQL like this is misplaced. The connection should be in an explicit transaction that rolls back if the user disconnects. To me this seems like the best scenario for the cleanup of what the connection has done. However, Jim's solution was interesting enough to warrant blogging about.