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.
Member
75 Points
513 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
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.
All-Star
123252 Points
10024 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:
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
75 Points
513 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)
All-Star
123252 Points
10024 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)
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm