sql server - how to call function inside a trigger? -
what problem @temp variable?
create function dbo.getnumofreviews2 (@email varchar(40)) returns int begin declare @numofreviews int select @numofreviews = count(*) dbo.reviews email = @email group email return @numofreviews end create trigger setdiscount on dbo.[contains] insert declare @orderid int declare @productid int declare @size varchar(15) declare @temp int if cursor_status('global','c_cursor')>=-1 begin deallocate c_cursor end declare c_cursor cursor select productid,orderid,size inserted begin open c_cursor fetch next c_cursor @productid,@orderid,@size while (@@fetch_status=0) begin @temp = dbo.getnumofreviews2(select billingemail dbo.orders orderid=@orderid) if (select count(*) dbo.[contains] orderid = @orderid) > 5 or (select sum(quantity) dbo.[contains] orderid=@orderid) > 10 or ( @temp )> 5 update [contains] set [savings%] = [savings%] + 0.05 orderid = @orderid , productid = @productid , size = @size fetch next c_cursor @productid,@orderid,@size end end
use select call scalar function
correct way be
select @temp = dbo.getnumofreviews2(billingemail) dbo.orders orderid=@orderid note: not advisable write big logic inside trigger. triggers should simple , fast otherwise dml operations slow. have used cursor should avoided @ cost. rewrite code using set based approach.
here set based approach code
;with cte (select c1.orderid dbo.[contains] c1 inner join inserted i1 on i1.orderid = c1.orderid group orderid having count(*) > 5 or sum(quantity) > 5 or @temp > 5) update c set [savings%] = [savings%] + 0.05 [contains] c inner join inserted on i.orderid = c.orderid , i.productid = c.productid , i.size = c.size , exists (select 1 cte c1 c1.orderid = c.orderid)
Comments
Post a Comment