none
Access New Values for Other Tables in Transaction from Trigger

    Question

  • Is it possible to access the new values for inserted records in a transaction from a trigger on a different table in the same transaction?
    Thursday, January 4, 2018 12:15 AM

All replies

  • Hi FireStorm2012,

    Could you please make a more detailed description? I'm not quite clear about which situation you have described. Or you could explain the functional description based on the sample table data.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 4, 2018 2:06 AM
    Moderator
  • If you mean that in one transaction you insert new rows into one table (call it TABLEA) and then insert rows into another table (call it TABLEB) and TABLEB has a trigger then is there a system provided way for the trigger on TABLEB to show the rows inserted in TABLEA?  If so, the answer is no.

    If you give us a description of what you are trying to do, the release of SQL Server you are running, sample tables (as CREATE TABLE statement(s)) sample data (as INSERT statement(s)),  and any other info you think we need and show us the result you would want from your process, we may well be able to provide suggestions on how you can accomplish what you want.

    Tom

    Thursday, January 4, 2018 4:24 AM
  • Sorry not sure on what you're asking here

    By

    new values for inserted records in a transaction from a trigger on a different table 

    do you mean from one table's trigger you want to get the newly inserted values within another table? If yes, its possible as long as you've some columns in the other table like audit trail columns (datemodified,datecreated etc) or you've a sequence based unique id column (mostly PK based on IDENTITY or SEQUENCE). If none of these exists, then its difficult to find the deltas (changes) done by current transaction. However you can capture this in a different table at the same time when you do the insertion if you want. For that you would need to create a table with fields to be captured and use OUTPUT clause in the insert to populate it with the new rows. This table can then be accessed with the trigger for the second table to get new rows

    so it will look like

    BEGIN TRAN InsertOps
    
    TRUNCATE TABLE NewInsertedData_Table1
    
    INSERT INTO Table1 (col1,col2,...)
    OUTPUT INSERTED.col1,INSERTED.col2... INTO NewInsertedData_Table1
    VALUES(value1,value2,..)
    
    ...
    
    INSERT Table2
    ....
    
    
    COMMIT TRAN InsertOps
    
    
    --trigger code
    
    CREATE TRIGGER <TriggerName>
    ON table2 
    FOR INSERT
    AS
    BEGIN
    ...
    
    --this will give you the newly inserted rows from table 1
    
    SELECT *
    FROM NewInsertedData_Table1
    ...
    END


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 4, 2018 5:23 AM