SQL Server: reset identity on any delete action

Question

Let's take a simple example. There's a table named test:

Now I'm gonna delete some data without reset the identity log:

When I insert new data, the new id will be 7:

I'd like that on every delete query the DBCC CHECKIDENT function was automatically called to reset the identity on table test. So, when I'd insert 'Purple', its id would be 4. Is there a way to do this?

Answer 1

You can create a DELETE TRIGGER and then use

SQL Fiddle Demo

CREATE TRIGGER sampleTrigger
    ON dbo.test
    FOR DELETE
AS
    DECLARE @maxID int;

    SELECT @maxID = MAX(id)
    FROM test;

    DBCC CHECKIDENT (test, RESEED, @maxID);
GO

So next insert will use 4.

But again this isnt something you should really be worry about it