Merge statement on a table with trigger in sqlserver 2008

I'am new to SQL Server 2008, as per the requirement we had to use MERGE statement for updating/inserting rows into a target table based on a particular condition.

t开发者_运维百科he issue is - the target table has trigger for update, in which we used inserted table like this

select StudentID from Inserted 

when i run the merge statement, it throws error at the trigger saying that the subquery returns multiple rows this is not allowed.

does this mean that the trigger is fired for all the records that are processed from the merge statement ? Can we make the trigger to fire for each record processed ?

Can any one please help me out.

thanks

Yes, in SQL Server, triggers fire once per statement, not once per row.

You haven't shown us enough of your SQL for me to suggest how to re-write your trigger. If it's something like:

UPDATE Table2 set ABC = DEF where ID = (select StudentID from Inserted)

then the re-write is as simple as:

UPDATE Table2 set ABC = DEF where ID in (select StudentID from Inserted)

More complex queries will require more work to correct them.

From your example (currently posted as an answer), you have:

if (select studentID from Inserted) <> (select studentId from deleted)

You could instead have something like:

if exists(select * from inserted i full outer join deleted d on i.StudentID = d.StudentID where i.StudentID is null or d.StudentID is null)

Which checks that you can join each row in inserted with a row in deleted, and vice versa.

However, if that's an early part of your trigger, it's likely there are other places in the code which assume only a single row in inserted and deleted. They'll all need changing.

To re-emphasize my first sentence - SQL Server triggers fire once per statement, not once per row. There's no way to change this, so you have to modify your code to cope with it.