SQL Server Trigger to Increment Id

In this SQL Server tutorial, we will understand how to define a trigger in SQL Server to increment the ID of the table while inserting records.

Recently, I was stuck in a situation where I got a table with a primary key value but not auto-increment. However, if the table holds thousands of records in that case determining the next ID value for the INSERT operation is quite difficult.

To resolve this situation in SQL Server, we can create a trigger that will fetch the maximum ID value. After this, it will increment the ID value for the next insert operation.

Here is the complete set of examples that we will discuss.

• SQL Server Trigger to Increment Id
• SQL Server trigger to increment id after insert
• SQL Server trigger to increment identity
• SQL Server trigger to increment id reset

SQL Server Trigger to Increment Id

In this section, we will discuss how to create a SQL Server trigger to increment ID value automatically. For this illustration, we will use the tblUSAStates table which does not have the auto-increment id column.

Here is the Transact-SQL code to create the tblUSAStates table and insert some records in it.

USE GeoNames
GO

create table tblUSAStates(
	id INT,
	state_name VARCHAR(50),
	state_code VARCHAR(50)
);
insert into tblUSAStates (id, state_name, state_code) values (1, 'South Carolina', 'SC');
insert into tblUSAStates (id, state_name, state_code) values (2, 'Pennsylvania', 'PA');
insert into tblUSAStates (id, state_name, state_code) values (3, 'Georgia', 'GA');

In the above SQL code, we have created the table named tblUSAStates. And also inserted 3 records in the same table.

Now, what if the same table holds thousands of records? In such cases, determining the last ID value is difficult. So, here we will create a SQL Server Trigger which will first fetch the maximum ID value. After this, increment the maximum ID value by 1 and then perform the INSERT operation.

Here is the SQL code for the trigger in SQL Server.

USE GeoNames
GO

CREATE OR ALTER TRIGGER trgInsertIncrementID
ON tblUSAStates
INSTEAD OF INSERT
AS
BEGIN
	DECLARE @id INT,
			@name VARCHAR(50),
			@code VARCHAR(50)

	SET @id = (SELECT MAX(id) FROM tblUSAStates) + 1
	SELECT @name = state_name, @code = state_code FROM inserted 

	INSERT INTO tblUSAStates(id, state_name, state_code)
	VALUES (@id, @name, @code)
	PRINT 'Record Insert with incremented ID'
END

• In this example, we have defined an instead of trigger named trgInsertIncrementID. This trigger will first fetch the maximum ID value and then increase the ID value by 1.
• In the end, it will use the state name, state code, and new ID value to insert the record in the tblUSAStates table.

After creating the trigger, if we try to perform the INSERT operation without even specifying the ID column value, the trigger will execute. And the trigger will automatically increment the ID value.

Here is the INSERT operation that we will perform on the table in SQL Server.

USE GeoNames
GO

INSERT INTO tblUSAStates (state_name, state_code)
VALUES ('Florida', 'FL')

After performing the above INSERT operation, we will the new record will ID value automatically set to 5.

So, in this section, we have understood how to create a trigger in SQL Server to increment ID value automatically.

Read: SQL Server Trigger On View

SQL Server trigger to increment id after insert

In this SQL Server section, we will understand how to create a SQL Server Trigger to increment ID after performing the UPDATE operation.

For this task in SQL Server, we will create an AFTER Trigger in SQL Server to increment the ID value. Again for this implementation, we are going to use the tblUSAStates table, but this time we will create an AFTER Trigger.

Here is the SQL query for the AFTER Trigger in SQL Server.

USE GeoNames
GO

CREATE OR ALTER TRIGGER trgAfterInsertIncrementID
ON tblUSAStates
AFTER INSERT
AS
BEGIN
	DECLARE @id INT
	SET @id = (SELECT MAX(id) FROM tblUSAStates) + 1

	UPDATE tblUSAStates
	SET id = @id
	WHERE id IS NULL
END

In the above example, we have created a trigger named trgAfterInsertIncrementID. This trigger will fire after the insert operation is been executed. Moreover, this trigger will fetch the maximum id value from the table and increment the max id value by 1.

In the last, it will use the UPDATE statement to set the id to a maximum ID value + 1.

Now, if we perform the INSERT operation on the table in SQL Server, it will insert the record in the table with the value of ID as NULL. Then trigger will be fired and it will update the value of the ID using the UPDATE statement.

USE GeoNames
GO

INSERT INTO tblUSAStates(state_name, state_code)
VALUES ('California', 'CA')

By performing the above INSERT operation, it will first insert the record with the value of ID as null. After this, it will use the trigger to increment the value of the ID.

Read: Trigger For Delete in SQL Server

SQL Server Trigger to increment id reset

In this section, we will discuss how to create a SQL Server Trigger to reset the auto-increment ID value. For this implementation, we will use the same tblUSAStates table.

However, the tblUSAStates table does not consist of the auto-increment column, so first, we will understand how to ALTER the table and add an auto-increment column to it.

Here are the SQL queries to add an auto-increment ID column to an existing table in SQL Server.

USE GeoNames
GO

--Adding new auto-increment column
ALTER TABLE tblUSAStates
ADD newID INT IDENTITY(10, 1)
GO

--Droping the existing ID column
ALTER TABLE tblUSAStates DROP COLUMN id
GO

--Renaming the new column to previous one
EXEC sp_rename 'tblUSAStates.newID', 'id', 'Column'

• In the above query, first, we added a new column to the table named newID. And this time, it is an auto-increment column.
• After this, we dropped the previous ID column from the table using the ALTER table statement.
• In the last, we use the sp_rename stored procedure to rename the newID column to just the id column.

Now that we have added the auto-increment column to the tblUSAStates table, let us move to the next phase. Next, we will understand how to create the AFTER DELETE Trigger to reset the value of the auto-increment IDENTITY column.

USE [GeoNames]
GO

CREATE OR ALTER   TRIGGER [dbo].[trgIncrementIDReset]
ON [dbo].[tblUSAStates]
AFTER DELETE
AS
BEGIN
	DBCC CHECKIDENT ('tblUSAStates', RESEED, 1)
END
GO

• In the above example, we have created a trigger that will be fired after performing the DELETE operation in SQL Server. So, the trigger will reset the auto-increment value to 1.
• After performing a delete operation, the next time we made an insert in the table, the id value will start from 1 instead of 10.

Here is the sample SQL for the DELETE and INSERT operations performed on the table in SQL Server.

DELETE FROM tblUSAStates
where id  = 12

INSERT INTO tblUSAStates (state_name, state_code)
VALUES ('New York', 'NY')

Now, if we check the table using the SELECT statement, we can observe that this time the increment ID is 2 instead of 14.

So, in this section, we have seen how to create a SQL Server Trigger to reset the auto-increment value.

Read: SQL Server Trigger Before Insert

SQL Server trigger to increment identity

In SQL Server, when we define an auto-increment table column using the IDENTITY, we don’t need to manually increment the value of ID. Instead, it will automatically increment the the value of ID column whenever we perform the insert operation on a table.

So, in such cases, we don’t usually need a trigger to increment identity column value in SQL Server. For more details related to the IDENTITY column in SQL Server, please refer to the following SQL Server tutorial: Identity Column in SQL Server.

Conclusion

So, in this SQL Server tutorial, we have discussed how to create a SQL Server Trigger to increment id value. Moreover, we have also discussed multiple examples whose list is given below.

• SQL Server Trigger to Increment Id
• SQL Server trigger to increment id after insert
• SQL Server trigger to increment identity
• SQL Server trigger to increment id reset

Also, take a look at some more SQL Server tutorials.

• How to execute Trigger in SQL Server
• SQL Server Trigger On View
• SQL Server Trigger to Increment Id
• How to Debug an SQL Server Trigger