Trigger in sqlserver 2012 to update Master with detail information

0

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

        
    asked by Horacio Xochitemol 14.06.2018 в 19:47
    source

    0 answers