Good day to all! Unfortunately, I’m not happy with SQL. In general, the problem is as follows. I have a small sign. There are there columns "Date" and "Change". The date is written over time, the date does not matter much, but the time is yes. There are 3 options: XXXX-XX-XX 00: 00: 00.000, XXXX-XX-XX 08: 00: 00.000 and XXXX-XX-XX 16: 00: 00.000. Depending on which of these times, the value of 1.2 or 3 should be in the "Change" column. I wrote the following code:

USE [DBCurs] GO /****** Object: Trigger [dbo].[Number_Smena] Script Date: 08.03.2017 15:58:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --==================================== -- Create database trigger template --==================================== ALTER TRIGGER [dbo].[Number_Smena] ON [dbo].[РабочийПроцесс] AFTER INSERT, UPDATE AS BEGIN IF (select cast(dbo.РабочийПроцесс.Дата as time) [time] FROM dbo.РабочийПроцесс )=convert(time,'00:00',101) UPDATE dbo.РабочийПроцесс SET Смена = 1 WHERE Смена IN(SELECT Смена) ELSE IF (select cast(dbo.РабочийПроцесс.Дата as time) [time] FROM dbo.РабочийПроцесс)=convert(time,'08:00',101) UPDATE dbo.РабочийПроцесс SET Смена = 2 ELSE UPDATE dbo.РабочийПроцесс SET Смена = 3 END

Then I take time out of datetime, check it and assign a value accordingly. If you run this code simply as a request, the values ​​change. But if you run it with a trigger, then an error occurs:

Strings have not been updated.

The data in row 3 was not recorded. Source of error: .Net SqlClient Data Provider. Error message: Subquery returned more than one value. This is prohibited when the subquery follows after =,! =, <, <=,>,> = Or is used as an expression.

The execution of this instruction has been interrupted.

As far as I understood by Google, my problem is that this trigger will work only if there is one row in the table, and if there are several of them, the query returns several values ​​and falls accordingly. Further, as I understand it, the problem is solved with the help of IN, JOIN or EXISTS commands, but I don’t understand how to apply them in my case. There are examples on the Internet, but unfortunately it does not work.

  • You need to decide which lines you want to change. And from which lines to take information about the date. It would be logical to do this not with all the rows in the table, but only with those that change. To do this, you need to get data from the inserted table and update only those rows that are present there. But in the inserted lines can also be a few! and therefore you need to either process them in a loop one at a time, or, more correctly, do everything in one request. And you did not specify in the question what kind of database you have and what version - Mike
  • MS SQL Server 2014. Now I did it like this, it seems to work: IF (select cast (Date as time) [time] FROM inserted) = convert (time, '00: 00 ', 101) - player
  • Indeed, too, is not an option, if you edit or add a few lines, then problems too. - player

0