Your Requirement is so unusual to use triggers, Triggers are meant for DDL or DML operations(UPDATE,DELETE,INSERT). As per my knowledge, you cant create triggers on a SELECT query !(Please feel free to correct me if my understandings are wrong at any point). First, Let us know what exactly is your requirement. One option is that if you are using a stored procedure to access those rows, then why cant you handle your requirement inside the Stored Procedure?
It's not possible and even if it was, it wouldn't be recommended because of the performance impact. What some companies does to fullfill some security requirements (like to prevent access to credit card transactions), are to activate a trace and store the trace data into a table in an encrypted database that no one has access to (besides an administration account that has a password that is combined by to different employees and the two pieces are stored in sealed envelopes in a safetybox) and . In SQL server 2008 it is possible to activate Auditing to accomplish the same functionality, but the audit data are stored in files. If you use auditing you need to prevent access to the folder.
Dawid - I would suggest you go about this from a different angle. 1. Restrict access to the table completely 2. Create a view that shows all data except the "sensitive" rows you want to know about 3. Create a view that shows all data 4. Allow access to the first view to all people 5. Allow access to the second view to authorised people. A further option would be to encapsulate the table access in a stored procedure and send an email when the "sensitive" data view was used. This way you are not waiting for someone to be naughty, you are restricting access to the sensitive data directly, so noone is tempted!