Trigger de multiples INSERTS

[candrade]

Hey espero me puedan resolver esta duda.

Actualmente estoy haciendo una aplicacion donde el usuario sube un archivo de EXCEL y éste lo convierte a Registros de dos tablas temporales de SQL Server 2008. (solucionado)

La primera de ella la llamo:
BasicaTemp y alimenta a una tabla llamada Basica.

La segunda de ellas:
ContenidoTemp alimenta 4 tablas:
Identificacion
Evaluacion
Disposicion
Aprovacion

OK hasta aqui todo esta solucionado ... el problema es que al final el contenido de las 5 tablas, (Basica, Identificaion, Evaluacion, Disposicion, Aprovacion) tienen que llenar una tabla llamada REPORTE

El problema es que cree un TRIGGER en cada tabla.
POR LOGICA ... solo tabla Basica genera un INSERT en REPORTE ... todas las demas son Updates ...

Mi pregunta es la siguiente:

¿Como puedo hacer un trigger que modifique REPORTE de un insert multiple

ASI ES COMO ESTOY LLENANDO LAS TABLAS:
ejemplo: Identificacion

INSERT INTO Identificacion ( ------- aqui todos los campos --------- )
(SELECT * FROM ContenidoTemp WHERE [Set] LIKE 'Identificacion%')

Debido a que no envio registro por registro, al dispararse el TRIGGER, (perdon por la redundancia), me marca un error de que el trigger no se puede completar debido a que el insert me envia mas de un valor.



AYUDA POR FAVOR!!!!

[iislas]

muestra tu trigger

[candrade]

OK pondre el PROCEDIMIENTO y el TRIGGER

CREATE PROCEDURE [dbo].[BajarReporte]
AS
BEGIN

/*Se ejecuta una aplicacion que convierte en NVARCHAR algunas columnas de EXCEL*/
Exec xp_cmdshell 'C:\Inetpub\wwwroot\eDMS\runner.exe'

/*Se lee la primera hoja del archivo y se guarda en una tabla temporal*/
SELECT * INTO BasicaT
FROM OPENDATASOURCE('Microsoft.ACE.OleDb.12.0', 'Data Source = C:\Inetpub\wwwroot\eDMS\Reporte.xls; EXTENDED PROPERTIES = Excel 5.0')...[BASIC_DETAILS$]

INSERT INTO [Basic](DefectID, Location, PartNumber, PartDescription)
(SELECT [DEFECT ID], [LOCATION], [PART NUMBER], [PART DESCRIPTION] FROM BasicaT WHERE [DEFECT ID] NOT IN (SELECT DefectID FROM [Basic]))

DROP TABLE BasicaT

/*Se lee la segunda hoja del archivo y se guarda en una tabla temporal
la cual posteriormente guarda datos en la tabla Identificacion*/

SELECT * INTO T
FROM OPENDATASOURCE('Microsoft.ACE.OleDb.12.0', 'Data Source = C:\Inetpub\wwwroot\eDMS\Reporte.xls; EXTENDED PROPERTIES = Excel 5.0')...[STEP_DETAILS$]

INSERT INTO Identificacion(DefectID, Serial, IDate, IGlobalLevel, IProductLevel, IProductLevel2, ILocalLevel, ILocalLevel2, ILocalLevel3, RMA, IComments)
(SELECT [DEFECT ID], [SERIAL NUMBER], [CREATION TIME], [GLOBAL SELECTION LEVEL 0], [PRODUCT SELECTION LEVEL 0], [PRODUCT SELECTION LEVEL 1], [LOCAL SELECTION LEVEL 0], [LOCAL SELECTION LEVEL 1], [LOCAL SELECTION LEVEL 2], [Local Data 1], [COMMENT]
FROM T WHERE [WORKFLOW STEP] LIKE 'Identifi%')


/*De la misma tabla creada se guardan los datos en la tabla de Evaluación*/

INSERT INTO Evaluacion(DefectID, EDate, EGlobalLevel, EGlobalLevel2, EProductLevel, EProductLevel2, ELocalLevel, ELocalLevel2, ELocalLevel3, EComments, EMPEApproval)
(SELECT [DEFECT ID], [CREATION TIME], [GLOBAL SELECTION LEVEL 0], [GLOBAL SELECTION LEVEL 1],[PRODUCT SELECTION LEVEL 0], [PRODUCT SELECTION LEVEL 1], [LOCAL SELECTION LEVEL 0], [LOCAL SELECTION LEVEL 1], [LOCAL SELECTION LEVEL 2], [COMMENT], [APPROVE]
FROM T WHERE [WORKFLOW STEP] LIKE 'Evalua%')

/*De la misma tabla creada se guardan los datos en la tabla de Disposición*/

INSERT INTO Disposicion(DefectID, DDate, DGlobalLevel, DGlobalLevel2, DProductLevel, DProductLevel2, DLocalLevel, DLocalLevel2, DLocalLevel3, DComments, HorasAplicadas, HorasRetrabajadas, HorasPerdidas, Origen, Efecto, DMPEApproval)
(SELECT [DEFECT ID], [CREATION TIME], [GLOBAL SELECTION LEVEL 0], [GLOBAL SELECTION LEVEL 1],[PRODUCT SELECTION LEVEL 0], [PRODUCT SELECTION LEVEL 1], [LOCAL SELECTION LEVEL 0], [LOCAL SELECTION LEVEL 1], [LOCAL SELECTION LEVEL 2], [COMMENT], [Global Data 1], [Global Data 2], [Global Data 3], [Local Data 2], [Local Data 1], [APPROVE]
FROM T WHERE [WORKFLOW STEP] LIKE 'Dispo%')

/*De la misma tabla creada se guardan los datos en la tabla de Aprovación*/

INSERT INTO Aprovacion(DefectID, Resultado, ADate, AComments)
(SELECT [DEFECT ID], [GLOBAL SELECTION LEVEL 0], [CREATION TIME], [COMMENT]
FROM T WHERE [WORKFLOW STEP] LIKE 'App%')

DROP TABLE T
END


Y Aqui pondre 2 TRIGGERS:


ALTER TRIGGER [dbo].[Reporte1] ON [dbo].[Basic]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON

INSERT INTO Reporte(DefectId, Location, PartNumber, PartDescription)
(SELECT DefectId, Location, PartNumber, PartDescription FROM inserted)
END


ALTER TRIGGER [dbo].[Reporte2] ON [dbo].[Identificacion]
FOR INSERT
AS
DECLARE @DefectId NVARCHAR(20) = (SELECT DefectId FROM inserted)
DECLARE @Serial NVARCHAR(20) = (SELECT Serial FROM inserted)
DECLARE @Date SMALLDATETIME = (SELECT IDate FROM inserted)
DECLARE @GlobalLevel NVARCHAR(100) = (SELECT IGlobalLevel FROM inserted)
DECLARE @ProductLevel NVARCHAR(100) = (SELECT IProductLevel FROM inserted)
DECLARE @ProductLevel2 NVARCHAR(100) = (SELECT IProductLevel2 FROM inserted)
DECLARE @LocalLevel NVARCHAR(100) = (SELECT ILocalLevel FROM inserted)
DECLARE @LocalLevel2 NVARCHAR(100) = (SELECT ILocalLevel2 FROM inserted)
DECLARE @LocalLevel3 NVARCHAR(100) = (SELECT ILocalLevel3 FROM inserted)
DECLARE @RMA BIT = (SELECT RMA FROM inserted)
DECLARE @Comments NVARCHAR(200) = (SELECT IComments FROM inserted)

BEGIN
SET NOCOUNT ON

UPDATE Reporte
SET Serial = @Serial, IDate = @Date, IGlobalLevel = @GlobalLevel, IProductLevel = @ProductLevel,
IProductLevel2 = @ProductLevel2, ILocalLevel = @LocalLevel, ILocalLevel2 = @LocalLevel2,
ILocalLevel3 = @LocalLevel3, RMA = @RMA, IComments = @Comments
WHERE DefectId = @DefectId

END

[iislas]

A ver, debes saber que en tu tabla INSERTED, puedes (creo que es el caso) traer mas de un registro, ¿cierto?

Por lo que la Declaracion de variables y asignacion de datos, no debe estar funcionando

[candrade]

Listo problema solucionado!


Efectivamente iislas ... la tabla INSERTED puede guardar multiples registros.

Lo pense y me lo corroboraste con lo que me dijiste, por lo que desarre el siguiente TRIGGER:

USE [DBedms]
GO

ALTER TRIGGER Reporte2 ON Identificacion
FOR INSERT
AS
DECLARE @total INTEGER = (SELECT COUNT(*) FROM inserted)
DECLARE @c INTEGER = 0
BEGIN
SET NOCOUNT ON

WHILE @c <= @total
BEGIN
DECLARE @DefectId NVARCHAR(20) = (SELECT TOP 1 DefectId FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @Serial NVARCHAR(20) = (SELECT TOP 1 Serial FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @Date SMALLDATETIME = (SELECT TOP 1 IDate FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @GlobalLevel NVARCHAR(100) = (SELECT TOP 1 IGlobalLevel FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @ProductLevel NVARCHAR(100) = (SELECT TOP 1 IProductLevel FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @ProductLevel2 NVARCHAR(100) = (SELECT TOP 1 IProductLevel2 FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @LocalLevel NVARCHAR(100) = (SELECT TOP 1 ILocalLevel FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @LocalLevel2 NVARCHAR(100) = (SELECT TOP 1 ILocalLevel2 FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @LocalLevel3 NVARCHAR(100) = (SELECT TOP 1 ILocalLevel3 FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @RMA BIT = (SELECT TOP 1 RMA FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))
DECLARE @Comments NVARCHAR(200) = (SELECT TOP 1 IComments FROM inserted WHERE DefectId IN (SELECT TOP (@c) DefectId FROM inserted ORDER BY DefectId ASC))

UPDATE Reporte
SET Serial = @Serial, IDate = @Date, IGlobalLevel = @GlobalLevel, IProductLevel = @ProductLevel,
IProductLevel2 = @ProductLevel2, ILocalLevel = @LocalLevel, ILocalLevel2 = @LocalLevel2,
ILocalLevel3 = @LocalLevel3, RMA = @RMA, IComments = @Comments
WHERE DefectId = @DefectId

SET @c = @c + 1
END

END
GO



Siempre habia odiado los "While" hasta hoy =D.

[iislas]

¿Porque no haces el UPDATE haciendo un INNER JOIN?

Código SQL:

UPDATE TABLE SET tt.colx = y.colx
FROM tutabla tt INNER JOIN inserted y 
ON tt.id = y.id
 

Te evitas el WHILE y aplicas las buenas practicas en el desarrollo de T-SQL