Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    31

    Unanswered: Selecting the last inserted row

    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
    AS

    DECLARE job_number_cursor CURSOR FOR
    SELECT JobNumber
    from asiapac702_test.dbo.tblCustServiceHistoryHdr
    where ServiceType= 'On-site' AND ServiceStatus = 'NEW' and DateModified=(SELECT MAX(DateModified) from asiapac702_test.dbo.tblCustServiceHistoryHdr)
    OPEN job_number_cursor
    FETCH NEXT FROM job_number_cursor INTO
    @job_number


    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!

  2. #2
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    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.

  3. #3
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    Hi gayamantra,

    Am I missing something?
    I do not see you referencing the table inserted in your trigger.

    It is the inserted table which contains the newly inserted rows.
    Just like mtracey says, though, it will contain all of your inserted rows if there are more than one.

    Cheers,
    Robert

  4. #4
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1
    Hi
    Use the prefix INSERTED.<table_name> in where clause to refere to that single row which is inserted into the table and due to which the trigger has fired. In that case u need not use CURSOR also.

    If u don't get it - script both the tables (A and B ) plus the necessary query also and email me @ gvphubli@mailcity.com I will do it for u.
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •