Condicion de autonumeracion

[Libras]

Explicado en codigo :)

Código SQL:

CREATE TABLE #temp
(
valor VARCHAR(30)
)
 
INSERT INTO #temp VALUES ('001-002-000157472')
INSERT INTO #temp VALUES ('001-002-000157473')
INSERT INTO #temp VALUES ('001-002-000157474')
INSERT INTO #temp VALUES ('001-002-000157469')
INSERT INTO #temp VALUES ('001-002-000157475')
 
DECLARE @param VARCHAR(20)
DECLARE @total INT
DECLARE @maximo INT
DECLARE @int_param INT
 
DECLARE @new_value VARCHAR(20)
SET @param='0003'
 
 
SELECT @total=COUNT(*) FROM(
SELECT SUBSTRING(valor1,charindex('-',valor1,1)+1,LEN(valor1)) AS total FROM(
SELECT SUBSTRING(valor,charindex('-',valor,1)+1,LEN(valor)) AS valor1 FROM #temp
) AS t1 
) t2 WHERE total LIKE @param + '%'
 
IF @total=0
BEGIN
  SET @int_param=CONVERT(INT,@param)
  SET @param=CONVERT(VARCHAR(20),@int_param)
  SET @new_value= '001' + '-' + '002-' + REPLICATE('0',3) + @param + '00001'
END  
ELSE
BEGIN
SELECT @maximo=MAX(CONVERT(INT,total)) FROM(
SELECT SUBSTRING(valor1,charindex('-',valor1,1)+1,LEN(valor1)) AS total FROM(
SELECT SUBSTRING(valor,charindex('-',valor,1)+1,LEN(valor)) AS valor1 FROM #temp
) AS t1 
) t2 WHERE total LIKE @param + '%'
SET @maximo=@maximo+1
SET @new_value= '001' + '-' + '002-' + REPLICATE('0',3) + CONVERT(VARCHAR(20),@maximo)
END  
INSERT INTO #temp VALUES (@new_value)
SELECT * FROM #temp
 
----------------------------------------------
 

ya lo que tienes que hacer es aplicar una logica parecida para un trigger o como lo vayas a manejar :)

saludos!