title: Updating Inserted record using triggerc
body: <p>I have a table where I need to update a column's value with the Identity of the Inserted record.</p>
  
  <p>Following is the trigger I have written: </p>
  
  <pre><code>CREATE TRIGGER [dbo].[UpdateRecordID]
  on [dbo].[Employee]
  AFTER INSERT
  AS 
  BEGIN   
      UPDATE dbo.Employee
      SET RecordID = (SELECT EmployeeID FROM INSERTED ) WHERE EmployeeID= (SELECT EmployeeID FROM INSERTED )
  END
  </code></pre>
  
  <p>The trigger is created successfully but when I Insert a record into the table I get the following error: </p>
  
  <blockquote>
    <p>Maximum stored procedure, function, trigger, or view nesting level
    exceeded (limit 32).</p>
  </blockquote>
  
  <p>If I don't put the where condition in the update statement and write the trigger as following:</p>
  
  <pre><code>ALTER TRIGGER [dbo].[UpdateRecordID]
  on [dbo].[Employee]
  AFTER INSERT
  AS 
  BEGIN   
      UPDATE dbo.Employee
      SET RecordID = (SELECT EmployeeID FROM INSERTED )
  END
  </code></pre>
  
  <p>I get the following error: </p>
  
  <blockquote>
    <p>Subquery returned more than 1 value. This is not permitted when the
    subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as
    an expression. The statement has been terminated.</p>
  </blockquote>
  
  <p>What is the best practice to Update a record to keep the Primary Key in another column so that I can add more records in the same table and keep them related?</p>
  
  <p>I can write the Update statement in the Stored Procedure where I am inserting the record using @@Identity but I wanted to do the same using a trigger.</p>