CREATE TABLE [dbo].[CUSTOMER_CONTACT_STD](
[SERIAL] [int] NOT NULL,
[PROFILE] [tinyint] IDENTITY(0,1) NOT NULL,
[PROFILE_NAME] [varchar](200) NOT NULL,
[NAME_CURRENT] [varchar](200) NOT NULL,
[MIDDLE_NAME_CURRENT] [varchar](200) NULL,
[SURNAME_CURRENT] [varchar](200) NOT NULL,
CONSTRAINT [PK_CUSTOMER_CONTACT_STD] PRIMARY KEY CLUSTERED
(
[SERIAL] ASC,
[PROFILE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Nick Ms>
-- Create date: <23/12/2012>
-- Description: <Fills up missing profile numbers in CUSTOMER_CONTACT_STD or goes on with the next available profile number>
-- =============================================
CREATE TRIGGER [dbo].[CUSTOMER_CONTACT_STD_Insert_PROFILE]
ON [dbo].[CUSTOMER_CONTACT_STD]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE dbo.CUSTOMER_CONTACT_STD
SET PROFILE = (
SELECT MIN(t1.PROFILE)+1
FROM CUSTOMER_CONTACT_STD t1
LEFT JOIN CUSTOMER_CONTACT_STD t2
ON t1.PROFILE = t2.PROFILE-1
WHERE t2.PROFILE IS NULL
)
FROM dbo.CUSTOMER_CONTACT_STD INNER JOIN INSERTED ins
ON CUSTOMER_CONTACT_STD.PROFILE = ins.PROFILE
END
GO
SELECT MIN(t1.ID)+1 FROM MY_TABLE t1
LEFT JOIN MY_TABLE t2
ON t1.ID = t2.ID-1
WHERE t2.ID IS NULL
-- Create test data
use demo
go
create table test1 (Serial int, profile tinyint);
go
insert into test1 values(123, 0);
insert into test1 values(123, 1);
insert into test1 values(123, 2);
insert into test1 values(123, 3);
insert into test1 values(123, 4);
-- create gap in the code
delete from test1 where serial = 123 and profile = 2
-- create trigger
CREATE TRIGGER [dbo].[CUSTOMER_CONTACT_STD_Insert_PROFILE]
ON [dbo].[test1]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE dbo.test1
SET PROFILE = (
SELECT MIN(t1.PROFILE)+1
FROM test1 t1
LEFT JOIN test1 t2
ON t1.PROFILE = t2.PROFILE-1
WHERE t2.PROFILE IS NULL
)
FROM dbo.test1 INNER JOIN INSERTED ins
ON test1.PROFILE = ins.PROFILE
END
-- Check existing data, there is a gap in row #2
select * from test1
Serial profile
----------- -------
123 0
123 1
123 3
123 4
(4 row(s) affected)
-- new insert comes along, using #5, Trigger should fire to update the gap
insert into test1 values (123, 5);
-- Check data after insert, the 5 is now a 2
select * from test1
Serial profile
----------- -------
123 0
123 1
123 2
123 3
123 4
(5 row(s) affected)
DROP TRIGGER [dbo].[CUSTOMER_CONTACT_STD_Insert_PROFILE] ;
go
-- create gap in the code
delete from test1 where serial = 123 and profile = 2
go
select * from test1
Serial profile
----------- -------
123 0
123 1
123 3
123 4
(4 row(s) affected)
-- Do the insert, fill the gap during the insert, not afterwards in a trigger
declare @prof tinyint;
SELECT @prof = MIN(t1.PROFILE)+1
FROM test1 t1
LEFT JOIN test1 t2
ON t1.PROFILE = t2.PROFILE-1
WHERE t2.PROFILE IS NULL;
insert into test1 values( 123, @prof)
go
select * from test1;
Serial profile
----------- -------
123 0
123 1
123 2
123 3
123 4
(5 row(s) affected)
SELECT t1.serial s, MIN(t1.profile)+1 p FROM [CUSTOMER_CONTACT_STD] t1
LEFT JOIN [CUSTOMER_CONTACT_STD] t2
ON (t1.profile = t2.profile-1 AND t1.serial = t2.serial)
WHERE t2.profile IS NULL
--AND t1.serial = whatever number is the user trying to insert, assumption is that it's known
GROUP BY t1.serial
drop procedure myinsert
go
create procedure myinsert (@serial int)
as
set nocount on
declare @prof tinyint;
SELECT @prof = (SELECT MIN(t1.PROFILE)+1
FROM test1 t1
LEFT JOIN test1 t2
ON t1.PROFILE = t2.PROFILE-1 and t1.SERIAL = t2.SERIAL
WHERE t2.PROFILE IS NULL)
FROM test1
WHERE SERIAL = @Serial;
if @prof is NULL set @prof = 0;
insert into test1 values( @serial, @prof)
go
truncate table test1;
exec myinsert 123
select * from test1;
Serial profile
----------- -------
123 0
(1 row(s) affected)
exec myinsert 123
select * from test1;
Serial profile
----------- -------
123 0
123 1
(2 row(s) affected)
exec myinsert 124
exec myinsert 124
select * from test1;
Serial profile
----------- -------
123 0
123 1
124 0
124 1
(4 row(s) affected)
exec myinsert 125
exec myinsert 125
exec myinsert 125
select * from test1;
Serial profile
----------- -------
123 0
123 1
124 0
124 1
125 0
125 1
125 2
(7 row(s) affected)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test1](
[serial] [int] NOT NULL,
[profile] [tinyint] NOT NULL,
[name] [varchar](50) NULL,
[surname] [varchar](100) NULL,
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
(
[serial] ASC,
[profile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
create procedure myinsert (@serial int)
as
set nocount on
declare @prof tinyint;
SELECT @prof = (SELECT MIN(t1.PROFILE)+1
FROM test1 t1
LEFT JOIN test1 t2
ON t1.PROFILE = t2.PROFILE-1 and t1.SERIAL = t2.SERIAL
WHERE t2.PROFILE IS NULL)
FROM test1
WHERE SERIAL = @Serial;
if @prof is NULL set @prof = 0;
insert into test1 values( @serial, @prof)
go
create procedure myinsert (@serial int, @name varchar(50), @surname varchar(100)
as
insert into test1 values( @serial, @prof, @name, @surname)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test1](
[serial] [int] NOT NULL,
[profile] [tinyint] NOT NULL,
[name] [varchar](50) NULL,
[surname] [varchar](100) NULL,
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
(
[serial] ASC,
[profile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
serial profile name surname
1 0 nick mar
2 0 john johnson
2 1 john johnson
2 3 john johnson
3 0 jack daniels
3 2 jack daniels
CREATE PROCEDURE test1_INSERT (@serial int, @name varchar(50), @surname varchar(100))
AS
SET NOCOUNT ON
DECLARE @profile tinyint;
SELECT @profile = (SELECT MIN(t1.PROFILE)+1
FROM test1 t1
LEFT JOIN test1 t2
ON t1.PROFILE = t2.PROFILE-1 and t1.SERIAL = t2.SERIAL
WHERE t2.PROFILE IS NULL)
FROM test1
WHERE SERIAL = @Serial;
IF @profile is NULL set @profile = 0;
INSERT INTO test1 values( @serial, @profile, @name, @surname)
GO
Msg 2627, Level 14, State 1, Procedure test1_INSERT, Line 17
Violation of PRIMARY KEY constraint 'PK_customer'. Cannot insert duplicate key in object 'dbo.test1'. The duplicate key value is (2, 1).
The statement has been terminated.
Why not just use an integer column? Wouldn't that solve the issue you're having ?