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
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”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.