Post reply

finding out what sproc/job modified table data using triggers

  • August 28, 2007 at 4:33 am

    #178247

    I have a table and i have no idea how it is modified. I have wrote this small trigger:

    CREATE

    TRIGGER dbo.testUpdateTrigger

    ON Adventureworks.dbo.Files

    AFTER INSERT,UPDATE

    AS

    BEGIN TRY

    INSERT INTO adventureworks.dbo.triggerlog( username, date)

    VALUES(suser_sname(), getdate())

    END TRY

    BEGIN CATCH

    END CATCH

    This only gets the username and date the table data was altered. is there anyway i can get more info, such as the job/sproc name?

     

    thanks all

  • August 28, 2007 at 5:36 am

    #729731

    There is not really a "call stack" that you could use to find out what stored procedure caused a particular table update. However, it is executed within the same transaction, so the transaction id will be shared.

    You could use a trace to gather information about the transactions and what is has been executed in them. Make sure you select the transactionid column. You can then in your trigger to add a message like:

    raiserror( 'table foo has been modified', 0, 1 ) with nowait

    And then can go through the trace and identify what caused the data modification.

    There is a performance impact though.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

Viewing 2 posts - 1 through 1 (of 1 total)

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