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

    Wink Unanswered: How to find out row that was just inserted?

    hi folks,

    Is it possible to find out the row which was just inserted in a table? I am creating a trigger and i m trying to access the row which was just inserted into the table. Any suggestions? Thanks a million!

    Gayathri

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Within a trigger you can access the newly inserted/updated/deleted fields by referencing virtual tables named:
    "inserted" - holds new and updated records
    "deleted" - holds deleted records

    You just reference these tables as you would any permanent table:

    select count(*)
    from YOURTABLE
    inner join INSERTED
    on YOURTABLE.PRIMARYKEY = INSERTED.PRIMARYKEY

    blindman
    Last edited by blindman; 11-06-03 at 22:35.

  3. #3
    Join Date
    Nov 2003
    Posts
    31
    My code is as follows

    DECLARE job_number_cursor CURSOR FOR
    SELECT JobNumber
    FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
    where JobNumber = Inserted.JobNumber
    OPEN job_number_cursor
    FETCH NEXT FROM job_number_cursor INTO
    @job_number

    CLOSE job_number_cursor
    DEALLOCATE job_number_cursor

    But i get an error saying
    "The column prefix 'Inserted' does not match with a table name or alias name used in the query. "

    Is there any other syntax that i am missing?

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    try this....

    DECLARE job_number_cursor CURSOR FOR
    SELECT JobNumber
    FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
    where JobNumber = (select Inserted.JobNumber from inserted)
    OPEN job_number_cursor
    FETCH NEXT FROM job_number_cursor INTO
    @job_number

    CLOSE job_number_cursor
    DEALLOCATE job_number_cursor

  5. #5
    Join Date
    Nov 2003
    Posts
    31
    Did modify to the code below but again it says "Invalid Object Name 'Inserted'



    Originally posted by rokslide
    try this....

    DECLARE job_number_cursor CURSOR FOR
    SELECT JobNumber
    FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
    where JobNumber = (select Inserted.JobNumber from inserted)
    OPEN job_number_cursor
    FETCH NEXT FROM job_number_cursor INTO
    @job_number

    CLOSE job_number_cursor
    DEALLOCATE job_number_cursor

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    this is the suggestion from MS...

    CREATE TRIGGER Trigger1 ON dbo.TableA
    FOR INSERT
    AS
    DECLARE @var1 INT
    DECLARE @VarA int

    SELECT @VarA = SELECT col2 FROM inserted
    DECLARE cursor1 CURSOR FOR
    SELECT col1
    FROM TableB
    WHERE col1 = @VarA

    OPEN cursor1
    FETCH NEXT FROM cursor1 INTO @var1

    CLOSE cursor1
    DEALLOCATE cursor1
    GO

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Question

    So modifying it for your purposes...

    DECLARE @SearchJobNumber int
    DECLARE @job_number int
    SELECT @JobNumber = SELECT JobNumber from inserted

    DECLARE job_number_cursor CURSOR FOR
    SELECT JobNumber
    FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
    where JobNumber = @JobNumber
    OPEN job_number_cursor
    FETCH NEXT FROM job_number_cursor INTO
    @job_number

    CLOSE job_number_cursor
    DEALLOCATE job_number_cursor

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is how to rewrite your original statement to avoid the error you got:

    DECLARE job_number_cursor CURSOR FOR SELECT JobNumber FROM Inserted
    OPEN job_number_cursor
    FETCH NEXT FROM job_number_cursor INTO
    @job_number

    CLOSE job_number_cursor
    DEALLOCATE job_number_cursor


    The big question is why you are putting a cursor in a trigger. This is the database equivalent of pouring sewage into your gas tank.

    blindman

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Cursor in trigger? Do not think it is good idea....

  10. #10
    Join Date
    Dec 2002
    Posts
    29
    I got the impression that things are getting on the wrong path. Summa summarum, the answer for your original question is given by Blindman in his first post:

    select count(*)
    from YOURTABLE
    inner join INSERTED
    on YOURTABLE.PRIMARYKEY = INSERTED.PRIMARYKEY


    But this should be used in the trigger written on the table which you are interested in. Got the picture?
    You probably get the error "Invalid Object Name 'Inserted'" because you are not using the code in the wright place.


    Best regards!

  11. #11
    Join Date
    Oct 2003
    Location
    Manchester UK
    Posts
    73

    IDENTITY

    Can you not just use the @@IDENTITY property?

    Or does this not work in a trigger?
    I haven't lost my mind, there's a backup on one of these floppies, somewhere.

Posting Permissions

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