title: Insert trigger preventing duplicates
body: <p>I have a table with a AutoIdentity column as its PK and a nvarchar column called "IdentificationCode". All I want is when inserting a new row, it will search the table for any preexisting IdentificationCode, and if any found roll back the transaction.</p>
  
  <p>I have written the folowing trigger:</p>
  
  <pre><code>ALTER trigger [dbo].[Disallow_Duplicate_Ids] 
  on [dbo].[tbl1]
  for insert
  as
  if ((select COUNT(*) from dbo.tbl1 e , inserted i where e.IdentificationNo = i.IdentificationNo ) &gt; 0)
  begin
  RAISERROR('Multiple Ids detected',16,1)
  ROLLBACK TRANSACTION
  end
  </code></pre>
  
  <p>But when inserting new rows, it always triggers the rollback even if there is no such IdentificationCode.</p>
  
  <p>Can any one help me please?</p>
  
  <p>thanks</p>