-1

The situation : I'm writing an AFTER INSERT trigger on a table, so I can access to INSERTED pseudo-table, if I have good memory. The trigger is a bit long, so I can't copy / pasta it here, but basically, I'd like to compare the datas of the row I'm inserting (representing a good) with the rows of another table (very similar, representing the wishes), in order to determine if the good inserted corresponds to someone's wishes.

So, I almost finished my trigger, but an error occurred. At a given point, I wrote :

-- Create and open a cursor
IF (@variable1 = INSERTED.MyField)
BEGIN
    -- some code
END

-- Deallocate and close my cursor

But I have the following error :

The multi-part identifier "INSERTED.MyField" could not be bound

I thought I could do it, as there is only one line in INSERTED as this moment (I'm right, don't I ?), but it seems I can't.

Can someone explain me why I'm wrong ?

PS : Yes, I've seen this link, or this one, or this one, but they all have a problem with JOIN, and I don't have any JOIN in here

Nimantha
  • 6,405
  • 6
  • 28
  • 69
AlexB
  • 7,302
  • 12
  • 56
  • 74
  • 2
    Ok first inserted can have more than one value in it, so how can you compare the value to a scalar variable. And you should not ever use a cursor ina trigger as they have to be performant or you will have blocking. – HLGEM Nov 04 '13 at 22:02
  • `INSERTED` can have more than one line, but I'm not inserting data in bulk, so I hop there can be only one line... False ? And if it's a bad practice to have a cursor, can you suggest me another way to compare the data of the inserted line with datas of another table ? A simple request doesn't seem to be an appropriate solution – AlexB Nov 04 '13 at 22:09
  • Use joins. You can never guarantee that only one record at a time is inserted. It is dangerous and irresponsible to assumet hat. – HLGEM Nov 04 '13 at 22:29
  • So, I have to join the `INSERTED` table and the table where I'm inserting data, right ? – AlexB Nov 04 '13 at 22:35

1 Answers1

-1

That error indicates SQL is trying to read 'INSERTED' as an alias for another table

IF (@variable1 = INSERTED.MyField)

Try the following to reference the inserted table

IF (@variable1 = MyField from inserted)

Using the inserted and deleted Tables: http://technet.microsoft.com/en-us/library/ms191300%28v=sql.105%29.aspx

This fixes the syntax and answers the question of why the error is occurring, but comparing inserted to a scalar variable is not recommended. As HLGEM stated, what if you have multiple values in the insert where some match and some don't.

Additionally, Cursors should be a last resort in SQL. In general, cursors are slower and hold up resources. SQL is a optimized for set-based operations and cursors don't leverage that. Without knowing exactly what you are trying to do in the cursor and how much data you are manipulating, I can't say definitely in this case.

One of the many discussions on StackOverflow about Cursors: stackoverflow.com/questions/743183/what-is-wrong-with-cursors

Community
  • 1
  • 1
John Turner
  • 45
  • 5
  • NEVER suggest comparing the field from insertted to a scalar vaiable. What if you have two records and one matches and one doesn't? – HLGEM Nov 04 '13 at 22:30
  • Yeah, it is not a good idea generally. The same can be said for cursors most of the time too, which is the first part of that code example. *If* the inserts are truly need to be one at a time that a cursor would suggest, it *might* work ok, but likely it won't be efficient. – John Turner Nov 04 '13 at 23:06
  • @AlexB without knowing exactly what you are trying to do in the cursor and how much data you are manipulating, I can't say definitely in this case. In general, cursors are slower and hold up resources. SQL is a optimized for set-based operations and cursors don't leverage that. One of the many discussions on StackOverflow about Cursors: http://stackoverflow.com/questions/743183/what-is-wrong-with-cursors – John Turner Nov 04 '13 at 23:19