title: SQL Server - How to reset identity column after amount of number
body: <p>I have a table "OrderInfo" with 4 columns: ID,Driver,Date,Increment_ID.</p>
  
  <p>The column "Increment_ID" is identity from 1 to 9999.
  The column "ID" based on value of the column "Increment_ID".(Ex: M-ddMMyy-0001).</p>
  
  <p>Like you can see the format value of column "ID", "ddMMyy" is day, month and year.
  For example: Today is 25/01/2018 so the value of my column "ID" is: M-251218-0001.</p>
  
  <p>I have created a trigger to reset identity column (Increment_ID) if its value reach 9999. But something went wrong and I couldn't insert new row unless I dropped that trigger. Here is my trigger: </p>
  
  <pre><code>CREATE TRIGGER trg_OrderInfo_ResetIdentityColumn ON OrderInfo
  FOR INSERT
  AS
  BEGIN
      DECLARE @max_increment_ID int = (SELECT MAX(od.Increment_ID) FROM OrderInfo od inner join inserted i on i.Increment_ID = od.Increment_ID )
      IF(@max_increment_ID is not null and @max_increment_ID=9999)
        DBCC CHECKIDENT ('OrderInfo',RESEED,0)
  END
  </code></pre>
  
  <p>For make sure to prevent duplicate primary key "ID" I chose the max value for "Increment_ID" is 9999. Actually I just have about 7000 rows a day. </p>
  
  <p>I will be grateful for any help from everyone. Thank you!</p>