Home / Questions / 348412
Accepted
Hackerman
Asked: 2020-04-23 10:51:04 +0800 CST 2020-04-23 10:51:04 +0800 CST

INSERT ONLY ONE RECORD

  • 772

I need that only one record can be inserted in the table, that is to say that if I send two inserts, it only accepts one and the other rejects it, I am taking the last batch of the table that goes in 18290, if I insert the next batch that is the 18291 should let insert and so on. but if I send two inserts that would be batch 18291 and 18292, it should not allow 18292 to be inserted, if not only 18291, that is, only let the next one be inserted

script that I carry

ALTER TRIGGER [dbo].[Lotes_Restringidos] 
ON  [dbo].[tbllotes] FOR INSERT 
AS

   DECLARE @Lote NVARCHAR(10)

SELECT TOP(1) @Lote = max(codlote) FROM tbllotes GROUP BY FECHA ORDER BY FECHA DESC 
          
   IF (@Lote = @Lote +1)  
   BEGIN

       ROLLBACK TRANSACTION
       RAISERROR ('no se puede insertar mas de un lote .',
                                                -- Message text.  
                  16,                           -- Severity.  
                  1                             -- State.  
                  ) 
   END

The problem with the script is that it allows you to insert more than one record when you should only allow the one that follows to be inserted.

issue

in an X server it has a database that is the one that collects the information of an app that is inside a food process, it would be the source data base, that the first action is to create a batch, that batch has an X quantity of products, Lot 1485 has 1000 products, those thousand products have a weight, when they finish weighing each product of this lot they close it, when they close it, an SSIS package is executed that sends the data from server X to server Y , server Yit has the same database with the same structure, this server is in charge of extracting reports but it settles one by one, that is, if from the package they send me two batches in a single execution and in the source database it goes to 1485 in 1487 and the information of 1486 would be lost , that's why in the destination database I have to accept only one by one if the package sends me 2 or 3 , I have to say wait send me the 1486 and in the next execution the 1487

sql-server

1 Answers

  1. Best Answer

    The solution for the scenario you pose, can be a trigger change the definition of your trigger After, for Instead Of.

    The particularity of this definition is that the trigger is started, instead of the instruction that triggers it, so you have to be the one to launch the insert.

    CREATE TABLE dbo.TblLotes (codLote int, Fecha date);
GO

CREATE TRIGGER dbo.Lotes_Restringidos
ON dbo.TblLotes
INSTEAD OF INSERT
AS

;With Cte As
(
    Select ROW_NUMBER() Over(Order by t.fecha desc) as row
    , t.codLote
    , t.Fecha
    From inserted t
    )
    Insert into dbo.TblLotes (codLote, Fecha)
    Select c.codLote, c.Fecha from Cte c
    where Row=1;
GO

    Its definition is identical, except for the mark INSTEAD OF instead of AFTER/FOR

    Therefore we generate a numerator of the rows that come in, and in the output of the common expression table we use only row 1.

    INSERT INTO dbo.TblLotes (codLote, Fecha)
values
(18291,'20190101'),
(18291,'20190102'),
(18291,'20190103');

GO

INSERT INTO dbo.TblLotes (codLote, Fecha)
values
(18292,'20190101'),
(18292,'20190102'),
(18292,'20190103');

GO

SELECT * FROM dbo.TblLotes;

    Result

    If several different batches could go in the same insertion statement, you only have to partition the number of rows, by the batchCod.

    ALTER TRIGGER dbo.Lotes_Restringidos
ON dbo.TblLotes
INSTEAD OF INSERT
AS
SET NOCOUNT ON
;With Cte As
(
    Select ROW_NUMBER() Over(Partition by t.codLote Order by t.fecha desc) as row
    , t.codLote
    , t.Fecha
    From inserted t
    )
    Insert into dbo.TblLotes (codLote, Fecha)
    Select c.codLote, c.Fecha from Cte c
    where Row=1;
GO

    The insert statement.

    INSERT INTO dbo.TblLotes (codLote, Fecha)
values
(18293,'20190110'),
(18293,'20190101'),
(18293,'20190105'),
(18294,'20190108'),
(18294,'20190101'),
(18294,'20190103'),
(18295,'20190101');
GO
SELECT codLote, Fecha FROM dbo.TblLotes;

    Departure

    Create Trigger

    • 1