Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2005
    Posts
    75

    Unanswered: sql 2005 DML triggers - capture SPID?

    is it possible to capture the SPID using an insert/delete trigger inside SQL 2005? All I can find are references to the EVENTDATA() handler inside the DDL triggers for SQL Server 2008 - that's supposed to contain a whole wealth of process information, but unfortunately SQL 2008 is years away from going production in my organization.

    I'm basically trying to create some audit trail triggers on a particular table in a DB. People are "accidentally" deleting things and nobody's owning up to it, so I need to find an alternative method. It's being done via an automated process triggered from a 32bit app, which I have no control over, so I have to be able to monitor the changes purely within SQL Server.

    A bonus would be me being able to pull off the same thing in SQL Server 2000 as well.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - spid won't help your auditing. They are scoped to the connection only. Check out suser_sname and related functions (I think they are collectively known as niladic functions). Your users will need to connect with their own log in (SQL or trusted conection) though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by pootle flump
    BTW - spid won't help your auditing. They are scoped to the connection only. Check out suser_sname and related functions (I think they are collectively known as niladic functions). Your users will need to connect with their own log in (SQL or trusted conection) though.

    thanks - unfortunately, there's no way to change how they connect to the DBs. But a bit back I was bored one day so I wrote an "enhanced" sp_who type stored proc that would use the hostname and hostprocess columns in master.dbo.sysprocesses and then use xp_cmdshell to execute a remote "tasklist.exe /V" with some filters on each of the open processes. Since 90% of my open connections are coming from Citrix servers with actual NT logins, I could grab all sorts of things from the local server (executable name, NT login, current client CPU utilization, CPU time). It's not the speediest thing in the world to execute (especially with the 800 or so average connections to each of the SQL Servers), but it's really come in handy a few times when I've had to troubleshoot application DB issues.

  5. #5
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by pootle flump

    thank you for this... I was looking at Create Trigger in BOL (I so rarely use triggers, so I always have to relearn these damn things whenever I need to write them), but it didn't mention @@SPID at all.

    sweet - this helps out a lot.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    There's an easier way...revoke dbo and ddladmin
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by Brett Kaiser
    There's an easier way...revoke dbo and ddladmin

    ?? These are regular row inserts/deletes that I want to log, not object level changes.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    DELETED - I rescanned your post and saw DDL. You are trying to trap DML....

    There is another problem. DML triggers fall within the transaction. If your long winded method of getting the username (which involves giving them perms to cmdshell) is not fast then you are going to be very unpopular.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2005
    Posts
    75
    Quote Originally Posted by pootle flump
    DELETED - I rescanned your post and saw DDL. You are trying to trap DML....

    There is another problem. DML triggers fall within the transaction. If your long winded method of getting the username (which involves giving them perms to cmdshell) is not fast then you are going to be very unpopular.

    yeah - I just realized that as I started writing... It's actually pretty speedy when you are just looking at one process (maybe a 1/2 second at most), plus the only modifications to this particular table are at the end of a 10+ minute process inside the app that doesn't happen all that often, so I'm not anticitpating the speed being an issue. The only time it really gets slow is when you have hundreds upon hundreds of processes you want to view at the same time.

    But it's the granting of cmdshell permissions that I'm not sure about.... I forgot about that. Hmm... I might have to re-examine my options to see if there's a better way of going about this. There's absolutely no way to get the application to connect any other way than it's current method, so this was the only way I could come up with to link the two together.

Posting Permissions

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