i have built sql job update value in table regarding condition in clause below:
update c set isloaded = 0 , loadingstate = 'l', issent = 0 container c c.isloaded = 1 , datediff(second, dateadd(hour, c.loadinginterval,c.entrydate), getdate()) / 3600.0 between c.loadinginterval , (c.loadinginterval + 2) but following error appeared when try run script:
msg 512, level 16, state 1, procedure updatecontainersstatistcs, line 9 subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression. statement has been terminated.
the reason because of trigger created on table. disabled trigger , working trigger script below:
create trigger [dbo].[updatecontainersstatistcs] on [dbo].[container] after update begin declare @daydatenow int set @daydatenow = (select count( daydate) containersstatstics constc daydate= (select convert(date, getdate())) , constc.contractorid =(select con.contractorid inserted con ) ) if @daydatenow>0 begin if update(loadingstate) begin if( (select i.loadingstate inserted i) = 'e') begin if ((select i.loadingstate inserted i)=(select loadingstate container cont cont.rfid = (select d.rfid deleted d))) begin update containersstatstics set emptycontainerscount= (select emptycontainerscount containersstatstics constc (daydate= (select convert(date, getdate())) , constc.contractorid =(select con.contractorid inserted con ))) + 1 , containerscount=(select count(id) container contractorid =(select con.contractorid inserted con )) contractorid =(select con.contractorid inserted con ) , daydate= (select convert(date, getdate())) end end else begin if (select count(emptycontainerscount) containersstatstics) > 0 begin if ((select i.loadingstate inserted i)=(select loadingstate container cont cont.rfid = (select d.rfid deleted d))) begin update containersstatstics set emptycontainerscount= (select emptycontainerscount containersstatstics constc (daydate= (select convert(date, getdate())) , constc.contractorid =(select con.contractorid inserted con ))) - 1 , containerscount=(select count(id) container contractorid =(select con.contractorid inserted con )) contractorid =(select con.contractorid inserted con ) , daydate= (select convert(date, getdate())) end end end end else if update(washingstatus) begin if( (select i.washingstatus inserted i) = 'e') begin if ((select i.washingstatus inserted i)=(select washingstatus container cont cont.rfid = (select d.rfid deleted d))) begin update containersstatstics set washedcontainerscount= (select washedcontainerscount containersstatstics constc ( daydate= (select convert(date, getdate())) )and constc.contractorid =(select con.contractorid inserted con )) + 1 , containerscount=(select count(id) container contractorid =(select con.contractorid inserted con )) contractorid =(select con.contractorid inserted con ) , daydate= (select convert(date, getdate())) end end else begin if (select count(emptycontainerscount) containersstatstics) > 0 begin if ((select i.washingstatus inserted i)=(select washingstatus container cont cont.rfid = (select d.rfid deleted d))) begin update containersstatstics set washedcontainerscount= (select washedcontainerscount containersstatstics constc ( daydate= (select convert(date, getdate())) )and constc.contractorid =(select con.contractorid inserted con )) - 1, containerscount=(select count(id) container contractorid =(select con.contractorid inserted con )) contractorid =(select con.contractorid inserted con ) , daydate= (select convert(date, getdate())) end end end end end else begin if update(loadingstate) begin if ((select i.loadingstate inserted i)= 'e') begin insert containersstatstics (emptycontainerscount ,contractorid,daydate,containerscount,washedcontainerscount)values (1,(select x.contractorid inserted x),(select convert(date, getdate())),(select count(id) container contractorid =(select con.contractorid inserted con )),0) end end else if update(washingstatus) begin if ((select i.washingstatus inserted i)= 'e') begin insert containersstatstics (emptycontainerscount ,contractorid,daydate,containerscount,washedcontainerscount)values (0,(select x.contractorid inserted x),(select convert(date, getdate())),(select count(id) container contractorid =(select con.contractorid inserted con )),1) end end end end
query totally correct reason of error trigger created on same table.
i disabled trigger on table , working well
Comments
Post a Comment