Trigger de multiples INSERTS

[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