I want to raise the following problem.
For reasons of agility, initially it is migrated to a table with name MtoRequerimiento
400 records with information to be able to perform the initial tasks with the following structure.
TABLE [dbo].[MtoRequerimiento](
[MtoRequerimientoId] [int] IDENTITY(1,1) NOT NULL,
[Partida] [int] NULL,
[Gpo] [nvarchar](10) NOT NULL,
[Gen] [nvarchar](10) NOT NULL,
[Esp] [nvarchar](10) NOT NULL,
[Dif] [nvarchar](10) NOT NULL,
[Var] [nvarchar](10) NOT NULL,
[Descripcion] [nvarchar](max) NOT NULL,
[Descripcion2] [nvarchar](max) NULL,
[Descripcion3] [nvarchar](max) NULL,
[Uni] [nvarchar](10) NOT NULL,
[Cant] [nvarchar](10) NOT NULL,
[Tipo] [nvarchar](10) NOT NULL,
[Clase] [nvarchar](10) NULL,
[Pmr] [float] NOT NULL,
[Comentarios] [nvarchar](max) NULL,
[MtoProcedimientoId] [int] NOT NULL,
[MtoUsuarioId] [int] NOT NULL,
[Maximo] [float] NULL,
[Minimo] [float] NULL,
[PrecioTira] [float] NULL,
[MaximoTira] [float] NULL,
[Fuentes] [int] NULL,
[RequiereRegistroSa] [bit] NULL,
[TipoPrecio] [nvarchar](max) NULL,
Well, now after I need to do a migration of about 3,000 records as a detail of the Mtorequerimiento
table, the detail has the following structure
TABLE [dbo].[DetalleZona](
[DetalleZonaId] [int] IDENTITY(1,1) NOT NULL,
[MtoRequerimientoId] [int] NOT NULL,
[Maximo] [float] NOT NULL,
[Minimo] [float] NOT NULL,
[MtoZonaId] [int] NOT NULL,
Now what I want is to shoot a tigger when an insert, update or delete is made in the table DetalleZona
to be able to update the fields Maximo
, Mimimo
of the table mtoRequerimiento
with the sum of each MtoRequerimientoId
.
I have the following tigger, but it updates all the records in the mykeep table.
ALTER TRIGGER [dbo].[UpdateMaximoMinimo]
ON [dbo].[DetalleZona]
FOR insert, update,delete
AS
BEGIN TRANSACTION
BEGIN TRY
UPDATE MtoRequerimiento SET Maximo = TUpdate.Maximo, Minimo= TUpdate.Minimo
FROM MtoRequerimiento t1, (SELECT T1.MtoRequerimientoId, SUM(t2.Maximo) Maximo,SUM(t2.Minimo) Minimo
FROM MtoRequerimiento t1, DetalleZona t2
WHERE t1.MtoRequerimientoId=t2.MtoRequerimientoId
GROUP BY t1.MtoRequerimientoId) TUpdate
WHERE t1.MtoRequerimientoId = TUpdate.MtoRequerimientoId
/* confirmo las modificaciones*/
COMMIT TRANSACTION
END TRY
BEGIN CATCH
/* Hay un error, deshacemos los cambios*/
ROLLBACK TRANSACTION
PRINT 'Se ha producido un error!'
END CATCH
They could guide me so that they are only the records that are inserted, deleted or modified from the table DetailZone