subquery returned more than 1 value

Question

I have an insert trigger that copies records to a history table when fired, based on certain criteria.   If a column is equal to a value, then I do some processing and ultimately insert the record into a history table.

 

If I do the below:

----------------

IF (select FIELDA from inserted) = 'ATEST'

----------------

this is ok unless there are multiple records, then I get

Msg 512, Level 16, State 1, Procedure ABCDEF, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

-----------

I can change the

IF (select FIELDA from inserted) = 'ATEST'

to include distinct

---------------

IF (select distinct FIELDA from inserted) = 'ATEST'

---------------

but, i don't know that the column will always be a distinct value, in which i think I would get

the same error as above.    I guess my question is, how do you get around this in a trigger?  Use a cursor?

 

 

  

Answer 1

try to use :

if exists (select 1 from inserted where FIELDA ='ATEST')

 

 

---

Best regards

Answer 2

More, you can use a query like this in your trigger :

try to use :

insert HistoryTable (fileds1,....)

select fileds1,.... from inserted where FIELDA ='ATEST'

---

Best regards

Answer 3

use top 1

Answer 4

More, you can use a query like this in your trigger :

try to use :

insert HistoryTable (fileds1,....)

select fileds1,.... from inserted where FIELDA ='ATEST'

---

Best regards

The important thing here which you are missing in your code sqlguy10 is the where clause which allows for multiple values.

In the Select part you could apply your processing if it is simple SELECT column1 X 4, etc

This method will ensure you are processing all the changes. If you change your query to use Top 1 you will only be catchin gteh first change.

Seth

 

 

---

http://lqqsql.wordpress.com

Answer 5

Thanks