When adding a record to a table, the first record is added, and when adding a second one, it shows that "the subquery returned more than one value". There is a trigger on the table.

USE [ERP] GO /****** Object: Trigger [dbo].[Заявка_ЖурнаПрих] Script Date: 09.05.2016 18:08:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Trigger [dbo].[Заявка_ЖурнаПрих] ON [dbo].[Заявка] FOR insert AS DECLARE @V_Puti nvarchar(50); SET @V_Puti = 'В пути'; SET nocount on while (Select Дата_поступления From Заявка) >= CONVERT (date, GETDATE()) begin Update Заявка Set Статус=@V_Puti where [№-заявки] in(select [№-заявки] from inserted) END
  • one
    Cannot use the query in this way while . I remember I needed a much more cumbersome design. Of course, since then, that may have changed. But after inserting the second record, the query will produce two lines, and only one value can compare the server at a time, which is what it says. - Sergey

1 answer 1

The logic of the query in while is not clear - depending on what you want to receive, you can use the following options:

 while (Select max(Дата_поступления) From Заявка) >= CONVERT (date, GETDATE())

or

 while (Select min(Дата_поступления) From Заявка) >= CONVERT (date, GETDATE())

or

 while exists (Select Дата_поступления From Заявка where Дата_поступления >= CONVERT (date, GETDATE()))

well, etc.