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
@black-army, Upvote for supporting you.
Your contribution cannot be approved because it does not follow the Utopian Rules.
You can contact us on Discord.
[utopian-moderator]