CURSOR UPDATE FOR NEW RECORD INSERTED IN BASE TABLE

  • Hi,

    I am working on the trade buy sell matching system for share broker. The requirement is that I am using cursor, and in the cursor I am inserting new records in the base table of the cursor. I want to run the same cursor continually for the newly added records also on recurring basis.

    sample is :

    /*

    CREATE TABLE TMP_TEST

    (ID INT, NAME VARCHAR(10))

    TRUNCATE TABLE TMP_TEST

    INSERT INTO TMP_TEST VALUES(1,'RAJIV1')

    INSERT INTO TMP_TEST VALUES(2,'RAJIV2')

    INSERT INTO TMP_TEST VALUES(3,'RAJIV3')

    INSERT INTO TMP_TEST VALUES(4,'RAJIV4')

    INSERT INTO TMP_TEST VALUES(5,'RAJIV5')

    INSERT INTO TMP_TEST VALUES(6,'RAJIV6')

    */

    DECLARE @ID INT, @NAME VARCHAR(10)

    DECLARE CUR CURSOR DYNAMIC

    FOR

    SELECT ID, NAME FROM TMP_TEST ORDER BY ID

    OPEN CUR

    FETCH NEXT FROM CUR INTO @ID, @NAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @ID

    PRINT @NAME

    PRINT '------------------'

    INSERT INTO TMP_TEST VALUES(@ID,'RAJIV' + CONVERT(VARCHAR(10), @ID))

    FETCH NEXT FROM CUR INTO @ID, @NAME

    END

    CLOSE CUR

    DEALLOCATE CUR

    This is a proforma of the cursor. The actual data is different.

    So anyone pls help me out.

    Thank you in anticipation.

    Shantaram

  • Hi

    Some questions:

    * Why is the usage of a cursor requirement?

    * What is the reason for the recursion?

    * Is the requirement that this shall be executed always when new data become inserted into your table? Trigger?

    Greets

    Flo

  • the change from your real needs to your psuedo code and fake tables makes the real requirement make no sense.

    in your example, you had 6 rows, and you insert the same identical 6 rows into the same table again, so there's 12 rows, 2 identical rows each with an ID between 1 and 6.

    can you show us your REAL requirement? as Florian noted, there was no need to use a cursor, unless this is an educational assignment to get you used to using cursors...but your code looked fine, so I doubt that was it.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ORIGINAL RECORDS

    ------------------

    lSrNo,tCltCd,tBSInd,lQty,lHedgeQty,lHedgeSrNo

    1,A007,B,50,0,0

    2,A007,B,155,0,0

    3,A007,B,120,0,0

    4,A007,S,100,0,0

    5,A007,S,120,0,0

    6,A007,B,50,0,0

    7,A007,S,100,0,0

    EXPECTED RESULT

    -----------------

    lSrNo,tCltCd,tBSInd,lQty,lHedgeQty,lHedgeSrNo

    1,A007,B,50,50,4

    4,A007,S,50,50,1

    2,A007,B,120,120,5

    3,A007,B,100,100,7

    6,A007,B,50,50,8

    5,A007,S,120,120,2

    7,A007,S,100,100,3

    8,A007,S,50,50,6

    9,A007,B,35,0,0

    10,A007,B,20,0,0

    As u see in the result SrNo 1 Buy Qty is adjusted against SrNo 4 Sell Qty and updated lHedgeSrNo=1 of original buy srno, and for remaining sell qty 50 one new sell record added as srno 8. This should continue till there is no buy/sell remains for matching. See buy srno 9 and 10 as there is no sell to match these qty.

    This is my main requirement.

    Regards,

    Shantaram

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply