This is just a general question. I have a table within which there is a column (status) to be updated based on the current date value. Is is possible to fire a trigger when the database opens or to fire the trigger every 5 (say) minutes? Ultimately, can a trigger be fired independently from user interaction, or must the user manipulate a table to get the trigger to fire?
A trigger fires for DML (INSERT/UPDATE/DELETE) operations. If you need something to run on a schedule or when the database "opens", then use a SQL Agent job.
Sounds more like you need a scheduled SQL Agent job to run daily to update the data. Triggers fire when data is inserted/deleted/updated.
Another possible alternative might be to make your column a computed column that uses GETDATE() to get the date part. But I am obviously speculating because I don't know how your data changes from day to day.