How to get identity of another table record inside after insert trigger caused by a transaction for current scope and Session?

Question

I have an AFTER INSERT trigger on a table named FileInformation. The trigger performs two updates on this table.

It works fine when FileInformation is inserted into solely. But when a record is inserted into this table in a transaction with another table from my application layer, one of the updates that is trying to get the Identity value from the other table and update the inserted record of FileInformation table with that value does not work and can not get the identity value from the record being inserted into the other table inside the transaction.

This is what my trigger looks like:

ALTER TRIGGER [Infrastructure].[UpdateFileInformationAfterInsert]  
ON [Infrastructure].[FileInformation]  
AFTER INSERT
AS
BEGIN
    DECLARE @InsertedCode AS int;
    DECLARE @SQLQuery AS nvarchar(MAX);
    DECLARE @DataBaseTableName AS nvarchar(100);
    DECLARE @DataBaseTableRowID AS uniqueidentifier;
    DECLARE @ProjectComponentID AS uniqueidentifier;
    DECLARE @BoundedContext AS nvarchar(200);
    DECLARE @Code AS int;

    SELECT
        @InsertedCode = Code, 
        @DataBaseTableName = DataBaseTableName, 
        @DataBaseTableRowID = DataBaseTableRowID, 
        @ProjectComponentID = ProjectComponentID 
    FROM
        inserted

    SET @BoundedContext = (SELECT Name 
                           FROM [Infrastructure].[BoundedContext] 
                           WHERE ID = (SELECT BoundedContextID 
                                       FROM [Infrastructure].[ProjectComponent] 
                                       WHERE ID = @ProjectComponentID))
    SET @SQLQuery = CONCAT('select @Code=Code from [', @BoundedContext ,'].[',@DataBaseTableName,'] where ID=''',@DataBaseTableRowID,'''')

    EXEC sp_executesql @SQLQuery ,N'@Code int output',@Code=@Code output

    UPDATE [Infrastructure].[FileInformation]
    SET DataBaseTableRowCode = @Code
    WHERE Code = @InsertedCode

    IF (SELECT Value FROM [Infrastructure].[ConfigurationSetting] 
        WHERE Name = N'FileSavingMethod') = N'0'
    BEGIN
        DECLARE @FileSavingRootPath AS nvarchar(MAX)
        SET @FileSavingRootPath = (SELECT Value 
                                   FROM [Infrastructure].[ConfigurationSetting] 
                                   WHERE Name = N'FileSavingRootPath')

        UPDATE [Infrastructure].[FileInformation]
        SET FileSavePath = CONCAT('~\', @FileSavingRootPath, '\', [Infrastructure].[GetSubDirectory] (@InsertedCode))
        WHERE Code = @InsertedCode
    END
END

As I mentioned, the first UPDATE in the code above does not work when a transaction with another table is being executed.

My question: I have read some answers about SCOPE_IDENTITY, @@IDENTITY, IDENT_CURRENT. Is it possible to get the identity value from the other table (by using its name like IDENT_CURRENT('tablename')) which in my code is @DataBaseTableName BUT inside the current scope and session?

Answer 1

IDENT_CURRENT should not be considered. It is not safe to use for concurrent activity.

Is NOT possible to get the identity value from the other table by using its name like IDENT_CURRENT('tablename'). With SQL Server 2016 you can consider using sp_set_session_context to set a variable that is within your session scope only.

Set using:

DECLARE @SI NUMERIC(38,0)= SCOPE_IDENTITY();
EXEC [sys].[sp_set_session_context] @key = 'BlaBlaKeyName'
                                   ,@value = @SI
                                   ,@read_only = 1;  

Retrieve using:

SELECT @SavedIdent = CONVERT(NUMERIC(38,0),SESSION_CONTEXT(N'BlaBlaKeyName'));