Trigger on select

Question

Trigger on select

Telammica

Ten Centuries

Points: 1288
More actions
July 29, 2008 at 10:28 am

#70973

Hi
Does anyone know if a trigger can be placed on a select somehow? I want an event to fire everytime an event (select, update, insert, delete) happens on a table.
Please any answer welcome.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

There is no "SELECT" trigger. You can have triggers on Insert, Update, and Delete. The only way I know of to audit selects is to set up a trace.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Telammica Ten Centuries Points: 1288 More actions · Answer 2

Thanks for the reply.

What I want to do is find out which objects are not in use so I could get rid of them in order to free up space on the db. For sp's it is simple, by logging to a table if it executes. Is there a simple way for discovering unused tables without using trigger?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Rename the table and see who complains? 😀

Seriously, there's no easy way. You'll have to trace the server for a while (a day, a week, a month) until you're happy that you have seen all of the queries that run then check all of those queries to see if they affect the table in question.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

GilaMonster (7/29/2008)
Rename the table and see who complains? 😀

I like it:D

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Greg Snidow SSCoach Points: 16108 More actions · Answer 5

GilaMonster (7/29/2008)
Rename the table and see who complains? 😀

Took out my comment. It was a joke, but could easily have been misconstrued as bad advice.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Telammica Ten Centuries Points: 1288 More actions · Answer 6

Haha -

Thanks for the replies.. but the complaints wont be so funny in a banking environment. 🙂

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Table sysdepends contains all objects used in procedures.

If you don't use ad-hoc SQL and table is not in sysdepends - that table is you candidate.

But take into account: sysdepends records got dropped every time a procedure is set to be recompiled (e.g. after altering but before 1st execution). So, don't make quick decisions.

_____________
Code for TallyGenerator

Telammica Ten Centuries Points: 1288 More actions · Answer 8

Thanks Sergiy

Do you know when exactly when the object will be inserted into sysdepends? The following select gives me more or less what i want for the usertables, but I need to know when the info will be inserted.

select b.name,* from sysobjects b

where b.id not in (select distinct c.depid

from sysdepends c)

and b.xtype = 'U'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Dependencies are entered when dependant items are creates. So if you create a stored proc that uses a particular table, when you create the proc, the relevant entries are added to sysdepends.

Be careful, sysdepends is not guaranteed to contain all dependencies

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Telammica Ten Centuries Points: 1288 More actions · Answer 10

thanks. Yeah - although it is not guaranteed, at least it gives me a rough list of some tables I might be looking at. Do you think I will be able to put a trace in profiler on only those tables that i suspect?

Dave Abraham-468315 Newbie Points: 6 More actions · Answer 11

Jack Corbett (7/29/2008)
There is no "SELECT" trigger. You can have triggers on Insert, Update, and Delete. The only way I know of to audit selects is to set up a trace.

You could have that trace log to a SQL table, then set up an INSERT trigger on that table.

Just make sure that your trace filters out whatever that trigger is doing, or you might end up with sort of an infinite loop where your trace inserts into a table, which runs a trigger which logs to another table which gets picked up by the trigger, etc. 🙂

John-150025 SSC Eights! Points: 862 More actions · Answer 12

Just search your code base for the tablename?

1. for database objects like views, triggers and udf's:

select * from syscomments where text like '%TableNameHere%'

2. then search other code that accesses db.

Edit: Unless I misunderstood and you were looking for tables that you know already exist in the code but want to see if they are being used.