title: SQL Server trigger insert causes lock until commit
body: <p>Having trouble with locks on a related table via triggers.</p>
  
  <p>I'm inserting into table <code>tCatalog</code> (this has a trigger to simply insert a record in another table <code>tSearchQueue</code>). The insert to <code>tCatalog</code> is inside a transaction that has a lot of other functions that sometimes takes several seconds. However, the <code>tSearchQueue</code> table is locked until the transaction can be committed. Is there a way to avoid this?</p>
  
  <pre><code>INSERT INTO [dbo].[tSearchQueue] (Processed, SQL, sys_CreateDate) 
      SELECT      
          0, 'Test ' + cast(CatalogID as varchar(10)), getdate()
      FROM        
          inserted 
  
  
  BEGIN TRAN t1
      DECLARE @catalogid int
  
      INSERT INTO tCatalog (ProgramID, sys_CreatedBy, ItemNumber, Description, UOMID) 
      VALUES (233, 1263, 'brian catalog4', 'brian catalog4', 416)
  
      SELECT @catalogid = SCOPE_IDENTITY()
  
      INSERT INTO tCustomAttributeCatalog (CatalogID, CustomAttributeID, DefaultValue, DefaultValueRead, sys_CreatedBy) 
      VALUES (@catalogid, 299, 'No', 'No', 1263)
  
      INSERT INTO tCustomAttributeCatalog (CatalogID, CustomAttributeID, DefaultValue, DefaultValueRead, sys_CreatedBy) 
      VALUES (@catalogid, 300, null, null, 1263)
  
  COMMIT TRAN t1
  </code></pre>