ok i have this situation and i am hoping someone can help me...
There are users inserting rows to TABLE A. What i want to do is if a particular row that was inserted matches my criteria, extract data from some of the colums and then insert into TABLE B.
I am using a trigger to achieve this, a portion of which i have given below.
CREATE TRIGGER SendMsgs ON asiapac702_test.dbo.tblCustServiceHistoryHdr FOR INSERT
DECLARE job_number_cursor CURSOR FOR
where ServiceType= 'On-site' AND ServiceStatus = 'NEW' and DateModified=(SELECT MAX(DateModified) from asiapac702_test.dbo.tblCustServiceHistoryHdr)
FETCH NEXT FROM job_number_cursor INTO
Now what i realise is that when this code is run, it selects many rows instead of just one. I thought it should be just the last inserted row since one of my criteria is to select the row whose DateModified was the latest. PLS ADVISE...been stuck with this for too long!
A trigger is not called for each row, but correctly as you've found out can be called once for multiple row updates. So if 10 rows have been updated at for all intensive purposes at the same time, the trigger could be initiated only the once.
Why can you not loop (via fetch cursor as your doing) through the rows, extract the information you need ?
Each record(s) returned in the trigger have at some stage been the last inserted row, so I'd have thought that you would have wanted to perform the same query and insert against them ?
So instead of the trigger being called 10 times, you now have the trigger being called the 1.