title: Get inserted uniqueidentifier from table B when its inserted from Table A insert trigger
body: <p>I wasnt sure how to open the title but let me explain what is my problem.</p>
  
  <p>I have two tables and both of them used uniqueidentifier as Id and they are auto generated by newsequentialid()</p>
  
  <p>Now, when i make an insert to table B, it runes Insert trigger and i do some specific things inside this trigger and i also insert some values to another table called A and i need to retreive this table A's inserted Id but i am unable to find a solution.</p>
  
  <p>Also let me explain why i need such a trigger. When a user creates an invoice with products in it where they have stock information, this trigger is responsible to create a stock transaction with header and detail about the products inserted (this stock detail table also has a trigger and it updates the warehouses) etc. etc. </p>
  
  <p>i hope this gives some hint what i am trying to do</p>
  
  <pre><code>CREATE TRIGGER [dbo].[IT_TBLDebitInvoiceDetails] ON [dbo].[TBLDebitInvoiceDetails] AFTER INSERT
  AS 
  BEGIN
      SET NOCOUNT ON;
  
      declare @cnt int;
      --HEADER
      declare @DocumentId uniqueidentifier;
      declare @OrganizationId uniqueidentifier;
      declare @Date date;
      declare @TermDate date;
      declare @DespatchDate date;
      declare @DespatchNo nvarchar(20);
      declare @WarehouseId uniqueidentifier;
      declare @CustomerId uniqueidentifier;
      declare @StockId uniqueidentifier;
      declare @CurrencyTypeId uniqueidentifier;
      declare @GrandTotal money;
      --Auditable
      declare @WhoCreated uniqueidentifier;
      declare @DateCreated datetime;
      declare @WhoUpdated uniqueidentifier;
      declare @DateUpdated datetime;
      --DETAIL
      declare @ProductId uniqueidentifier;
      declare @Quantity decimal(18, 2);
      declare @ProductType int;
  
      SELECT TOP(1) @OrganizationId = OrganizationId, @DocumentId = A.Id, @Date = A.[Date], @TermDate = A.TermDate, @DespatchDate = A.DespatchDate, @DespatchNo = A.DespatchNo,
      @WarehouseId = A.WarehouseId, @CustomerId = A.CustomerId, @GrandTotal = A.GrandTotal,
      @WhoCreated = A.WhoCreated, @DateCreated = A.DateCreated, @WhoUpdated = A.WhoUpdated, @DateUpdated = A.DateUpdated
      FROM TBLDebitInvoices AS A
      INNER JOIN inserted AS B ON A.Id = B.InvoiceId
  
      /* CHECK STOCK TRANSACTION */
      SELECT @cnt = COUNT(*) FROM inserted AS A
      INNER JOIN TBLProducts AS B ON B.Id = A.ProductId
      WHERE B.ProductType != 1
  
      --we have products for stock, create stock header
      IF(@cnt &gt; 0)
      BEGIN
          INSERT INTO TBLStocks (OrganizationId, TransactionType, DocumentType, DocumentId, [Date], DeliveryDate, DeliveryNo, SourceWarehouseId, CustomerId, [Description], WhoCreated, DateCreated, WhoUpdated, DateUpdated, IsDeleted)
          VALUES (@OrganizationId, 5, 0, @DocumentId, @Date, @DespatchDate, @DespatchNo, @WarehouseId, @CustomerId, '', @WhoCreated, @DateCreated, @WhoUpdated, @DateUpdated, 0);
  
          SELECT @StockId = ???????;
      END
  
      INSERT INTO TBLStockDetails (StockId, ProductId, [Value])
      SELECT @StockId, ProductId, SUM(Quantity) FROM (
          SELECT A.ProductId AS ProductId, A.Quantity  FROM inserted AS A
          INNER JOIN TBLProducts AS B ON B.Id = A.ProductId
          WHERE B.ProductType = 0
          UNION ALL
          SELECT C.IngredientID, A.Quantity * C.Quantity FROM inserted AS A
          INNER JOIN TBLProducts AS B ON B.Id = A.ProductId
          INNER JOIN TBLProductRecipes AS C ON C.ProductId = B.Id
          WHERE B.ProductType = 2
      ) AS T1
      GROUP BY ProductId;
  
      UPDATE TBLDebitInvoices SET StockId = @StockId WHERE Id = @DocumentId;
  
      /* CHECK DC TRANSACTION */
      INSERT INTO TBLDebitCreditTransactions (TransactionType, DocumentType, DocumentId, PaymentStatus, [Date], Amount, AccountType, AccountId, CurrencyTypeId)
      VALUES (1, 0, @DocumentId, 0, @TermDate, @GrandTotal, 0, @CustomerId, @CurrencyTypeId);
  END
  GO
  </code></pre>
  
  <p>inside this trigger i have this insert:</p>
  
  <pre><code>INSERT INTO TBLStocks (OrganizationId, TransactionType, DocumentType, DocumentId, [Date], DeliveryDate, DeliveryNo, SourceWarehouseId, CustomerId, [Description], WhoCreated, DateCreated, WhoUpdated, DateUpdated, IsDeleted)
  VALUES (@OrganizationId, 5, 0, @DocumentId, @Date, @DespatchDate, @DespatchNo, @WarehouseId, @CustomerId, '', @WhoCreated, @DateCreated, @WhoUpdated, @DateUpdated, 0);
  
  SELECT @StockId = ???????;
  </code></pre>
  
  <p>and i need the Id inserted to this table so i can use its id to insert its row elements.</p>