Composite Primary Key AutoIncrement Trigger on SQL

It is an important requirement that a field in SQL be unique. This is the "Primary Key".

A unique field can be made auto-increment by classic SQL methods.

When we define two columns as keys in sql, this is "Composite Key".

This is not possible with traditional SQL auto-increment methods when we need an auto-increment like the one below.

Example:
ColumnA | ColumnB

10001 | 1

10001 | 2

10001 | 3

10002 | 1

10002 | 2

To solve this problem, you need to write an SQL Trigger as follows;

Create TRIGGER [dbo].[Location_Composite_Key]
ON [dbo].[Location]
INSTEAD OF INSERT
AS 
BEGIN

    DECLARE @location_id as int;
    DECLARE @company_id as int;
    DECLARE @location_name as nvarchar(50);

    SET @location_id = ISNULL((SELECT MAX(LocationCode) FROM Location WHERE CompanyCode IN (SELECT CompanyCode FROM INSERTED)), 0);
    SET @location_id = @location_id + 1;
    
    select @company_id=CompanyCode, @location_name=LocationName from INSERTED
    
    insert into Location (CompanyCode,LocationCode,LocationName) 
        Values (@company_id,@location_id,@location_name)

END

---

Posted on Utopian.io - Rewarding Open Source Contributors

---