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

• • Reply
  zhyanadil.it...

  Member

  75 Points

  512 Posts

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

  Jul 23, 2020 07:11 PM|zhyanadil.it@gmail.com|LINK

  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)
  
  
  end

  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.

• • Reply
  limno

  All-Star

  123240 Points

  10019 Posts

  Moderator

  Re: trigger update if more than one record updated get subquery returns more than one value in sq...

  Jul 23, 2020 07:16 PM|limno|LINK

  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
  as 
  begin
  
  insert into tb2(series)
  select series from inserted
  
  end
  

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  zhyanadil.it...

  Member

  75 Points

  512 Posts

  Re: trigger update if more than one record updated get subquery returns more than one value in sq...

  Jul 23, 2020 08:53 PM|zhyanadil.it@gmail.com|LINK

  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)

• • Reply
  limno

  All-Star

  123240 Points

  10019 Posts

  Moderator

  Re: trigger update if more than one record updated get subquery returns more than one value in sq...

  Jul 23, 2020 10:24 PM|limno|LINK

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

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm