Sometimes you have to do some things considered pretty strange in your SQL Server.
This is one of those things.
Another SQL Fast and Dirty tip!
Just like mosquitoes, triggers are sticking around.
A few years ago I was working at a client, and all sorts of issues were coming up where the application wasn’t able to reset a counter in a table accurately enough by the application. There were too many connections, or users, or processes, or something. It wasn’t a great project, but I did get some neat little tricks figured out while there.
In this particular situation we were required to perform a daily identity reset in a table.
What?
Basically reset the identity value in a table each day without removing the previous data.
Obviously the identity field in this case was not unique, but it was setup with an auto-increment to make the application incrementation problem easier to handle. It was a great case of knowing the various tools you have available in the application and database to find the best solution you can (at the time anyhow).
At least that’s how I remember this specific scenario.
Regardless, this process can be used to reset an identity at a specific time. In this case it was each new day.
To accomplish this a trigger was used.
I know, I know, TRIGGERS! *GASP*
You do know there really isn’t anything wrong with them right?
So long as you know what they are doing and don’t go overboard with them.
If they were truly as evil as they are made out to be, then we would have found a way to kill them off a while ago. (Like mosquitoes. I still don’t know why they are around)
Anyhow, here’s the sample code to play with. Obviously you need to change the date to make it work for you, but it is kinda fun.
-- create our test table
-- note the identity column is not unique. If you did that this process would not work.
CREATE TABLE test
(
test INT IDENTITY(1, 1) ,
test2 INT ,
test3 DATE DEFAULT GETDATE()
)
GO
-- INSERT 50 random records, using an earlier date
INSERT test
( test2, test3 )
SELECT RAND() * 1000 ,
'2015-05-18'
GO 50
-- check the table contents
SELECT *
FROM test
GO
-- Ooooooo, the trigger. It checks on each insert to see if the data has changed (not in table already) then resets the identity
-- if needed and inserts like normal.
CREATE TRIGGER dbo.test_trg ON dbo.test
INSTEAD OF INSERT
AS
SET NOCOUNT ON;
IF NOT EXISTS ( SELECT TOP 1
1
FROM test
WHERE test3 = CONVERT(DATE, GETDATE()) )
BEGIN
DBCC CHECKIDENT (
'test'
,RESEED
,0
);
END
INSERT dbo.test ( test2 )
SELECT test2 FROM inserted;
GO
-- now to check out the table and how it works
SELECT * FROM test
GO
-- add another value manually to increment the identity
INSERT test ( test2 )
VALUES ( 923456 )
GO
-- now to check out the table for the new record
SELECT * FROM test
GO
-- When done playing, get rid of your table!
DROP TABLE dbo.test
GO
And there it is. This can easily be adapted to reset on different scenarios, not just the date. It also shows a way a simple trigger can solve some issues being had in applications.
Now, I’m not saying to go nuts and put triggers everywhere instead of using the application or other SQL objects (proceudres, functions,e tc) that could do better. But don’t outrule triggers, just because they are bad.
Sometimes a little bad is just what is needed.
If you use or adapt this to work in a different way, let me know in the comments. It is always fun to see how others use this type of code!