a "trigger" in SQL Server (and other RDBM systems I know) is fired on a database action like insert, update or delete.

I think your problem looks more like a batch job since you probably want to identify expired accounts without requiring an insert/update to fire the SQL trigger.

If my understanding is correct then you might consider creating a SQL Agent job to run periodically to query for expiring accounts and do the needful.