An Error with an Update Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

An Error with an Update Trigger

Author

Topic

davidr63
Starting Member

6 Posts

Posted - 2009-03-23 : 11:22:22

I have a table that contains the geographic information of crimes commited in the city.
I created an Insert and update trigger so that when the geographic information is Verified, other fields in the table will be updated too.
After I successfully tested the trigger by updating address information into the table I recieve an error.
The error returned is:

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

Here is the trigger code:

Create TRIGGER geo_lwmain ON lwmain
AFTER INSERT, UPDATE AS

DECLARE @tempid char(12)
DECLARE @temppoly1 char(8)
DECLARE @temppoly2 char(8)
DECLARE @temppoly3 char(8)
DECLARE @temp4 char(8)

set @tempid = (SELECT lwmainid FROM inserted)

set @temppoly1 = (SELECT replace(poly1,char(0),'') FROM inserted)
set @temppoly2 = (SELECT replace(poly2,char(0),'') FROM inserted)

set @temppoly3 = (SELECT replace(poly3,char(0),'') FROM inserted)
if substring(@temppoly2,2,1)= '2'
set @temp4 = 'RED'
else
if substring(@temppoly2,2,1)= '3'
set @temp4 = 'BLUE'
else
if substring(@temppoly2,2,1)= '4'
set @temp4 = 'GOLD'
else
if @temppoly2= '152'
set @temp4 = 'GREEN'
else
set @temp4 = ' '

update lwmain set tract = @temppoly1,
zone = @temppoly2,
neighborhd = @temppoly3,
district = @temp4,
reportarea = substring(@temppoly2,1,1)
where lwmainid = @tempid

How can I prevent this error from occuring?
I know it's the Update portion of the trigger causing the problem because when I removed the Update from the statement the procedure works.

Thanks for any insight into this.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-23 : 12:35:08

I would guess that where you're assigning value to @tempid, @temppoly1 and @temppoly2 you're getting more than row back from inserted. I would suggest changing the body of the trigger to just return select * from inserted and see what that shows you in QA. That should give you an idea of why you have multiple rows.

Mike
"oh, that monkey is going to pay"

davidr63
Starting Member

6 Posts

Posted - 2009-03-23 : 12:54:09

Thanks I will give that a try.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 13:00:02

That is exactly what's happening. Loose all the variables and just JOIN to inserted so that you can handle multi-row transactions:


update l set 
       l.tract              = replace(i.poly1,char(0),'')
       ,l.zone              = replace(i.poly2,char(0),'')
       ,l.neighborhd        = replace(i.poly3,char(0),'')
       ,l.district          = case
                                   when substring(replace(i.poly2,char(0),''), 2, 1)= '2' then 'RED'
                                   when substring(replace(i.poly2,char(0),''), 2, 1)= '3' then 'BLUE'
                                   when substring(replace(i.poly2,char(0),''), 2, 1)= '4' then 'GOLD'
                                   when i.poly2 = '152' then 'GREEN'
                                   else ''
                                   end
       ,l.reportarea        = substring(@temppoly2,1,1)
from   lwmain l
inner join inserted i on i.lwmainid = l.lwmainid

EDIT:
I see that I missed replacing the var for Reportarea...I'll leave that to you :)

Be One with the Optimizer
TG

davidr63
Starting Member

6 Posts

Posted - 2009-03-23 : 15:22:13

Thanks mfemenel and TG. this a been a great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources