Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    15

    Unanswered: How to find source, that inserted data into a table?

    Hi all!

    I have a lot of stored procedures, which can insert data into the table.
    How can I find which stored procedure inserted the certain row?

    I wrote a trigger:
    CREATE TRIGGER dbo.TraceTheProcedure
    ON dbo.SomeTableToTrace
    FOR INSERT, UPDATE AS
    Insert into DedugTable
    Select getdate() as EventTime,
    object_name(@@procid) as Source,
    object_id
    From inserted
    But all I got is:
    date [NULL] 239
    date [NULL] 240
    etc..
    So, why does object_name(@@procid) always return null instead of object name? And how to find the name of the procedure?
    If I store just @@procid - I get lont integer != 0

    regards
    Last edited by CbIP; 09-20-12 at 08:43.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    It might that the @@procid gets the NULL-value because of the trigger. Maybe a trigger does not provide a procid?
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Jul 2012
    Posts
    15
    No, @@procid is a system variable.
    And if I try to store @@procid instead of object_name(@@procid), it will get a long integer.

Posting Permissions

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