Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: How can you tell if an application is writing to a database?

    I was all set to build some triggers on some modified date tables when in the last minute I found out that the application (built in C#) was controlling the after update trigger.

    Is there a tool you can use in SSMS to see if there is a connection set up like this?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not sure that I understand your question...

    Triggers are part of a table (technically the trigger is owned by a schema, but that's just splitting hairs). The trigger itself is logically "owned" by the table, not by a user or an application.

    You can see triggers as part of the table (so SSMS | SQL Server | Databases | MyDatabase | Tables | MyTable | Triggers is the full navigation to see them).

    I prefer to use a SQL snippet to find the little beggars, something like:
    Code:
    SELECT *
       FROM sys.triggers AS tr
       JOIN sys.tables AS t
          ON (t.object_id = tr.parent_id)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Sorry if I explained myself poorly.

    I was watching videos on building out triggers and assembilies. I ended up deciding to go with Triggers, but I found out later it looks like the application is firing the after update triggers, not the DBMS.

    How can you tell that a software program is delivering the updates to the tables and not the database?
    Is this that common? The last position I was at did the same thing, they used the application (C#) to update the fields.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The only way a trigger is "triggered" is through write operation.
    It cannot be executed separately.

    Therefore your "after update" is being executed because of an UPDATE command against the object.

    There is no way to bypass this without disabling the trigger. It doesn't matter if the update is through T-SQL or through a C# application.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure, but I think you may be working with a different definition of "trigger" than we normally do. By "trigger" do you mean some event that is triggered by a business action (such as entering an order triggers a stock quantity lookup), or do you mean a bunch of code tied on to a table that will execute on updates to that table? Or do you mean that all of the updating is done by the application, but some of it is done as an afterthought (or second followup update to the same row) after the main update?

    As a side note, triggers can be a very dangerous thing. They will execute on any update to the table, regardless of who or what is updating the table. Triggers that contain many actions, or long running actions can have a serious impact on the performance of your database.

  6. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Quote Originally Posted by MCrowley View Post
    updating is done by the application, but some of it is done as an afterthought (or second followup update to the same row) after the main update?
    This right here.

    I'm assuming the application is writing to the database using C# code, not SQL.

    I think......

    Thanks for your time.
    Last edited by VLOOKUP; 01-13-15 at 11:38.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, all updates (or selects for that matter) are done on SQL with T-SQL commands. The C# layer may have some hard-coded queries embedded in it, or could be generating strings of T-SQL code to be issued to the server. The generation of the T-SQL may be obscured by an ORM layer, or a data layer class, but it is there. The best way to find out what an application is doing (from SQL Server's point of view) is to use SQL Profiler to monitor the traffic coming in to SQL Server. You should really only do this in a controlled environment (QA or Dev), as the amount of data you can get back can be pretty staggering, if you have a busy server. It gets difficult to filter out the traffic you are interested in from all of the other noise that the other users will be generating.

  8. #8
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    MCrowley thanks for the follow up. I have a stagging environment I can run profiler in. Let me try that, thanks again for your time I appreciate it!

    @PatP Thanks for the query that validated the table didn't have a trigger on it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •