Professional Web Applications Themes

Concantonated Keys - Microsoft SQL / MS SQL Server

Please can any one help? I have two tables a Header and a Detail Table. The header table has a PK called Forecast_No This has a foreign key relationship with a concantonated primary key in the Detail table consisting of the Forecast_No and a Forecast_Line_No. For instane Header.Forecast_No = 1 Detail.Forecast_No = 1 + Deatil.Forecast_Line_No = 1 The problem I have is how to have the Forecast_Line_No as part of the primary key but to reset its auto-increment for every new Forecast_No. In this way I can create the master detail relationship I am looking for and the Detail information ...

  1. #1

    Default Concantonated Keys

    Please can any one help?

    I have two tables a Header and a Detail Table.

    The header table has a PK called Forecast_No
    This has a foreign key relationship with a concantonated
    primary key in the Detail table consisting of the
    Forecast_No and a Forecast_Line_No.

    For instane

    Header.Forecast_No = 1

    Detail.Forecast_No = 1 +
    Deatil.Forecast_Line_No = 1

    The problem I have is how to have the Forecast_Line_No as
    part of the primary key but to reset its auto-increment
    for every new Forecast_No.

    In this way I can create the master detail relationship I
    am looking for and the Detail information would read

    Foreacst_No/Line_No as below.

    1/1
    1/2
    1/3

    2/1
    2/2
    2/3

    I hope this makes sense! and that someone can help me.

    Thanks

    In advance

    Simon



    Simon Guest

  2. #2

    Default Re: Concantonated Keys

    Create an Identity column on the Detail table (let's call it detail_id), and
    then create a trigger on the detail table as follows:

    CREATE TRIGGER trga_i_Detail ON detail AFTER INSERT
    AS
    UPDATE D
    SET Forecast_Line_no =
    (SELECT COUNT(*) FROM Detail d1
    WHERE d1.Forecast_no = i.forecast_no
    AND d1.detail_id <= i.detail_id)
    FROM Detail D
    INNER JOIN inserted i
    ON d.detail_id = i.detail_id


    "Simon" <co.uk> wrote in message
    news:077a01c34bb5$30ab43c0$gbl... 


    Jacco Guest

  3. #3

    Default Re: Concantonated Keys

    Jacco,

    Thanks for that works a treat. But can you explani to me
    what is going on here!

    Where has the insert table come from? (is it a table?)

    Many Thanks

    Simon.


     
    it detail_id), and 
    message [/ref]
    as [/ref]

    >
    >
    >.
    >[/ref]
    simon Guest

  4. #4

    Default Re: Concantonated Keys

    Hi Simon,

    The 'inserted' table is a virtual table that is only available in triggers
    that holds the rows that are going to be inserted (in case of an INSERT
    trigger) or the new versions of the rows (in case of an UPDATE trigger). It
    has it's counterpart in the 'deleted' virtual table that holds the rows that
    are going to be deleted (in case of an DELETE trigger) or the old versions
    of the rows (in case of an UPDATE trigger). You can find more inforamtion
    about them if you look for CREATE TRIGGER in Books Online.

    In this case I use it to make sure that only the Detail that has just been
    inserted gets updated. You could also recalculate the line_no's for the
    complete table every time a new row gets inserted with:

    UPDATE Detail
    SET Forecast_Line_no =
    (SELECT COUNT(*) FROM Detail d1
    WHERE d1.Forecast_no = Detail.forecast_no
    AND d1.detail_id <= Detail.detail_id)

    but that will get quite time consuming if the table grows.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "simon" <com> wrote in message
    news:061601c34c37$dd796aa0$gbl... 
    > it detail_id), and 
    > message [/ref]
    > as [/ref]
    > I 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Jacco Guest

Similar Threads

  1. Determining all shortcut keys in use (including Function keys)?
    By Jeff_Bowell@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 1
    Last Post: December 31st, 07:01 PM
  2. Bests keys to use?
    By necromanthus in forum Macromedia Director 3D
    Replies: 2
    Last Post: September 10th, 05:58 PM
  3. Bests keys to use?
    By Newt99 in forum Macromedia Director 3D
    Replies: 0
    Last Post: September 9th, 03:01 PM
  4. Replies: 0
    Last Post: July 16th, 12:52 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139