troubleshooting Question

SQL Server: INSERT trigger failing for one of two columns of composite PK.

Avatar of N M
N MFlag for Luxembourg asked on
Microsoft SQL Server
24 Comments1 Solution502 ViewsLast Modified:
Hello

I have a table with composite key, of two columns, identity both. Here are the facts:
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]

The business logic is simply that each customer may have many contact profiles, i.e. customer 123 has in this table profile 123.0, 123.1, 123.2, customer 78 has only profile 78.0 and customer 9832 has profiles 9832.0, 9832.1, 9832.2, 9832.3, 9832.4 etc..

The first number is the SERIAL and the second the PROFILE. Both identities, seed 1 increment 1. As you can see in the previous description, there is a possibility to open for a customer some profiles missing some numbers (gap), for example, 123.0, 123.1 and 123.3 (missing 123.2).

For this problem I want to add a trigger which seems to work (?) in single column identities/PKs but here I got the annoying error:
Msg 8102, Level 16, State 1, Procedure CUSTOMER_CONTACT_STD_Insert_PROFILE, Line 16
Cannot update identity column 'PROFILE'.

Here is my code so far:
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

I also tried SET IDENTITY_INSERT ON and OFF but doesn't work. Any help would be greatly appreciated. Platform is SQL Server 2012 Ent but I think this would be working as well on the 2008 R2 (I think)


Thanks in advance all
ASKER CERTIFIED SOLUTION
Steve Wales
Senior Database Administrator

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 24 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 24 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros