Triggers y claves duplicadas

Este pequeño articulo pretende dar una ligera visión sobre el uso de los triggers para poder controlar errores de clave duplicadas. Muchos os preguntareis porque vamos a controlar nosotros las claves duplicadas si tenemos Primary Keys e índices únicos que ya se encargan de esto por nosotros.
Una de las razones que me llevo a realizar esta prueba, que ahora pongo en vuestro conocimiento, fue la casualidad y la curiosidad (que orden lleva SQL Server cuando se inserta un registro en una tabla).
Me puse manos a la obra, el mono de trabajo y música....
Los motivos principales para este estudio son 3'5: - saber cuales son las claves que se están intentando insertar de forma duplicada en una tabla - evitar mensajes de error por errores 2627 - controlar la inserción masiva de datos desde otras fuentes - y tb el "tapar" pequeños despistes en nuestras aplicaciones
Adjunto los scripts necesarios para realizar la practica. Creo que en el script están bien explicados todos los pasos.
La idea es básica, creo un Trigger del tipo INSTEAD OF INSERT que se ejecuta siempre y en vez de cada INSERT que se lance sobre mi tabla. En este Trigger hago una comprobación de los datos de la PK que se están intentando insertar en la tabla, si es así guardo esta información en una tabla temporal POPO (para tener un histórico) en caso contrario prosigo con la inserción. Una característica de este tipo de Triggers es que solo se ejecutan una vez por INSERT de manera que no se producen ejecuciones anidadas de Triggers del tipo INSERT.
Como podréis ver el ejemplo es bastante sencillo, era mi intención, y las aplicaciones las tendréis que ir buscando vosotros (no lo voy a hacer yo todo :-) )

Los pasos a ejecutar el Script son:

-- Creamos la BBDD de trabajo
CREATE DATABASE Temporal
GO

-- Usamos la BBDD
USE Temporal
GO

-- Creamos la tabla de trabajo
CREATE TABLE [dbo].[tabla] (
	[Numero] [int] NOT NULL ,
	[Texto] [varchar] (10) COLLATE Modern_Spanish_CI_AI NULL 
) ON [PRIMARY]
GO

-- Creamos una clave primaria
ALTER TABLE [dbo].[tabla] ADD 
	CONSTRAINT [PK_tabla] PRIMARY KEY  CLUSTERED 
	(
		[Numero]
	)  ON [PRIMARY] 
GO


-- Creamos la tabla de trabajo auxiliar
CREATE TABLE [dbo].[Popo] (
	[Numero] [int] NOT NULL ,
	[Texto] [varchar] (10) COLLATE Modern_Spanish_CI_AI NULL ,
	[Fecha] [Datetime] 
) ON [PRIMARY]
GO


-- Creamos el Trigger INSTED OF INSERT sobre la tabla de trabajo
CREATE TRIGGER trgIns ON [dbo].[tabla] 
INSTEAD OF  INSERT
	
AS

-- Me guardo las que ya estan en la Tabla
INSERT INTO Popo SELECT Numero,Texto,GetDate() FROM Inserted I 
  WHERE EXISTS(SELECT Numero FROM Tabla WHERE Numero=I.Numero)

-- Me guardo los intentos de insrtar claves duplicadas mediante conjuntos de resultados
INSERT INTO Popo SELECT Numero,Texto,GetDate() FROM Inserted I 
  WHERE EXISTS(SELECT numero FROM inserted WHERE numero=i.numero GROUP BY numero HAVING COUNT(*)>1)

-- Inserto las que no estan en la tabla y en el caso que esten intentando meter un conjunto de resultados (via UNION ALL) no meto ninguno de 
-- los que ya vinieran duplicados en este origen.
INSERT INTO Tabla SELECT Numero,Texto FROM Inserted I 
  WHERE NOT EXISTS(SELECT Numero FROM Tabla WHERE Numero=I.Numero) 
    AND EXISTS(SELECT numero FROM inserted WHERE numero=i.numero GROUP BY numero HAVING COUNT(*)=1)

GO



-- Metemos algunos registros en la tabla que no van a producir valores duplicados
INSERT INTO dbo.Tabla VALUES (1,'Uno')
INSERT INTO dbo.Tabla VALUES (2,'Dos')
INSERT INTO dbo.Tabla VALUES (3,'Tres')
INSERT INTO dbo.Tabla VALUES (4,'Cuatro')
INSERT INTO dbo.Tabla VALUES (5,'Cinco')

-- Ahora utilizamos una sentencia del tipo UNION ALL, para insertar un conjunto de resultados
INSERT INTO dbo.tabla 
  SELECT 1,'uno' 
   UNION ALL 
  SELECT 2,'dos' 
   UNION ALL
  SELECT 1,'uno' 
   UNION ALL 
  SELECT 8,'dos'


-- Ahora forzamos duplicados
INSERT INTO dbo.Tabla VALUES (1,'Uno')
INSERT INTO dbo.Tabla VALUES (2,'Dos')
INSERT INTO dbo.Tabla VALUES (3,'Tres')
INSERT INTO dbo.Tabla VALUES (4,'Cuatro')

-- Vemos como van las tablas
SELECT * FROM dbo.tabla
SELECT * FROM dbo.popo

-- Para vaciarlas
DELETE FROM dbo.tabla

-- Creamos un Procedimiento Almacenado
CREATE PROCEDURE dbo.Inserta
AS
	-- Voy a meter un registro duplicado
	INSERT INTO dbo.Tabla VALUES (4,'Cuatro')
	-- Ahora uno que sea diferente y que produciria un error 2627
	INSERT INTO dbo.Tabla VALUES (5,'Cinco')
GO

-- Ejecutamos el SP
EXEC dbo.Inserta
GO

-- Eliminamos la BBDD de trabajo
USE MASTER
GO

DROP DATABASE Temporal
GO

Por supuesto que este procedimiento en términos de rendimiento pues no es la mejor solución, en un próximo estudio veremos las diferencias en cuanto a este punto se producen. Agradezco cualquier tipo de comentario, constructivo y/o destructivo y espero que os haya servido para algo.

Fichero con el código de ejemplo: mafoj70_Triggersyduplicados_SCRIPT.zip - 2 KB