Problems with Insert SQL Server

Asked

Viewed 79 times

0

I am looking for a solution to solve my problem. When I ask for 2 items of the same type on my site it inserts 2 times the same item. I already tried to create a TRIGGER with AFTER INSERT to check if there is already that order number but without success

I’ve already tried: INSERT INTO Lojas.dbo.ComprasSucc (Usuario,Codigo_Produto,Quantidade,Numero_Pedido,Produto_Name,Data) VALUES (?,?,?,?,?,GETDATE()) ON DUPLICATE KEY UPDATE Numero_Pedido=Numero_Pedido+1

in my website’s PHP code too but it can’t send.

I would like to check if there is already an equal order number within the table before inserting. I tried: IF NOT EXISTS (SELECT Numero_Pedido FROM ComprasSucc WHERE Numero_Pedido = @Numero_Pedido)

But I couldn’t get him to check if it actually existed. I don’t know enough about SQL Server.

The order numbers that are sent to the database are Andom (Random).

also tried MAX(ID) but if you place 2 different orders at the same time it only performs the last.

sql-server

  • Have you ever thought of defining Numero_Pedido as the primary key in the table ComprasSucc?

  • Only one can and my table already has a main ;(

  • In this case, Constraint UNIQUE will solve.

  • I tried but when I make 2 purchase in the same cart it generates the same Order Number ID so it is sent only 1

  • All indicates that this is a problem of your application and not the database.

1 answer

0

From the statement I understood that the problem is in the generation of the order number, and for different applications the same order number was used.

One solution is to use an order number generator as detailed in article "Generation of numerical sequences". One way to implement is through SEQUENCE, which is a user-defined object that generates a sequence of numerical values according to the specification with which the sequence was created. It can be increasing or decreasing, with defined interval, and can also repeat the sequence, in cycles.

Another form of implementation is the use of a sequence table, which is the use of auxiliary table to store the last value generated from the sequence. A specific procedure is created to get the next order number and it should be triggered once for each order, then the same number is repeated for each item of the same order.

Here’s an example:

-- código #2.15
-- próximo valor disponível
declare @Prox_Valor int;

BEGIN TRANSACTION;

EXECUTE dbo.GetSequence 
     @nome_seq= 'dbo.Pessoa.ID_Pessoa',
     @valor = @Prox_Valor output;

INSERT into dbo.Pessoa (ID_Pessoa, Nome_Pessoa)
  values (@Prox_Valor, 'Paulo Antunes');

COMMIT;
go

As stated in the chapter "Analysis of methods" of the article I mentioned, the worst implementation is the type "MAX (column) +1", because it is not immune to competition of proceedings.

The subject of numerical key generation is extensive but is well explained in the article "Generation of numerical sequences". Careful reading of it will show you ways to solve your current problem.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.