Post reply

Trigger on select

  • 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.

  • July 29, 2008 at 10:39 am

    #849972

    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

  • July 29, 2008 at 11:12 am

    #850008

    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?

  • July 29, 2008 at 1:43 pm

    #850098

    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

  • July 29, 2008 at 1:47 pm

    #850102

    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

  • July 29, 2008 at 1:53 pm

    #850107

    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.

  • July 29, 2008 at 2:46 pm

    #850148

    Haha -

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

  • July 29, 2008 at 4:23 pm

    #850195

    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

  • July 30, 2008 at 1:20 am

    #850280

    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'

  • July 30, 2008 at 1:36 am

    #850285

    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

  • July 30, 2008 at 1:50 am

    #850289

    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?

  • May 18, 2009 at 1:30 pm

    #996588

    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. 🙂

  • May 18, 2009 at 1:59 pm

    #996614

    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.

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

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