CODEWITHSUNDEEP
sql-serverselecttriggers
chamara
chamara

Reputation: 12709

Fire triggers on SELECT

I'm new to triggers and I need to fire a trigger when selecting values from a database table in sql server. I have tried firing triggers on insert/update and delete. is there any way to fire trigger when selecting values?

Upvotes: 4

Views: 23686

Answers (3)

eidgenossen
eidgenossen

Reputation: 129

SpectralGhost's answer assumes you are trying to do something like a security audit of who or what has looked at which data.

But it strikes me if you are new enough to sql not to know that a SELECT trigger is conceptually daft, you may be trying to do something else, in which case you're really talking about locking rather than auditing - i.e. once one process has read a particular record you want to prevent other processes accessing it (or possibly some other related records in a different table) until the transaction is either committed or rolled back. In that case, triggers are definitely not your solution (they rarely are). See BOL on transaction control and locking

Upvotes: -2

Vallabh Patade
Vallabh Patade

Reputation: 5110

No there is no provision of having trigger on SELECT operation. As suggested in earlier answer, write a stored procedure which takes parameters that are fetched from SEECT query and call this procedure after desired SELECT query.

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

There are only two ways I know that you can do this and neither are trigger.

  1. You can use a stored procedure to run the query and log the query to a table and other information you'd like to know.
  2. You can use the audit feature of SQL Server.

I've never used the latter, so I can't speak of the ease of use.

Upvotes: 4

Related Questions