Can I tell SQL Server to "do something" when a connection is closed?
SQL Server provides a built-in mechanism to support application locking. The locks supported are Shared, Update, IntentShared, IntentExclusive, and Exclusive. Deadlock detection is also built-in, though application lock deadlocks do not terminate the active transaction - you need to check the result code returned from the system procedure call. Books Online documentation:

APPLOCK_MODE
APPLOCK_TEST
sp_getapplock
sp_releaseapplock

Locks associated with the session are automatically released when the session is logged out.

Community Wiki answer generated from a question comment by @JonSeigel