trigger update if more than one record updated get subquery returns more than one value in sql RRS feed

  • Question

  • User-1634604574 posted

    i have this update on table tb1 which update  more than one filed and also i have this trigger on that table tb1

    update query

    update tb1 set age=22 where gender=female

    here is my trigger

    create TRIGGER [dbo].[TR_TB_Log_update_tb1]
    ON tb1
    after UPDATE
    as begin
    declare @user varchar(max);
    declare @date_time varchar(max);
    declare @series varchar(max)
    set @series=(select series from inserted )
    insert into tb2(@series)

    i get this error on that highlight code

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Thursday, July 23, 2020 7:11 PM

All replies

  • User77042963 posted

    Don't use variable for these columns' value. It will not work correctly.

    Try this:

    create TRIGGER [dbo].[TR_TB_Log_update_tb1]
    ON tb1
    after UPDATE
    insert into tb2(series)
    select series from inserted

    Thursday, July 23, 2020 7:16 PM
  • User-1634604574 posted

    also i get the same error because i used it in another variable

    set @date_time=(select top 1 date_time from TB_Log where series=(select series from inserted) order by date_time desc)

    Thursday, July 23, 2020 8:53 PM
  • User77042963 posted

    One more time: Don't use variable ! (inside your trigger)

    Thursday, July 23, 2020 10:24 PM