Professional Web Applications Themes

preventing duplicate row insertion from asp.net app - ASP.NET General

Data is stored in SQL Server 2000. One table is Person another Item. Each row in the Person table may have associated with it several rows in the Item table. The Item table has a field called 'type'. Business rule: for certain values of this field, at most one row may exist (per Person) in the Item table. The DB is accessed from an ASP.NET app and more than one user may try to insert an Item of the same type (and for the same Person) at the same type. It would be very easy to ensure the above business ...

  1. #1

    Default preventing duplicate row insertion from asp.net app

    Data is stored in SQL Server 2000. One table is Person another Item.
    Each row in the Person table may have associated with it several rows
    in the Item table. The Item table has a field called 'type'. Business
    rule: for certain values of this field, at most one row may exist (per
    Person) in the Item table.

    The DB is accessed from an ASP.NET app and more than one user may try
    to insert an Item of the same type (and for the same Person) at the
    same type. It would be very easy to ensure the above business rule
    using a trigger (on the Item table) or a stored procedure that would
    do inside something like this:
    IF (NOT EXISTS (SELECT ... FROM Item)) INSERT INTO Item...

    Please note that, because of internal coding standards, I cannot
    modify the DB and I cannot access the database from my app through
    direct SQL statements (e.g., like the one above). I can only access
    the Item table through stored procedures that perform inside them
    simple selects and inserts on the table.

    Assuming the stored procedures (for SELECT and INSERT on the Item
    table) are called inside the same method, is there a way to satisfy
    the above mentioned business rule (ensure mutual exclusion) at the app
    level? Would specifying the isolation level as serializable at the app
    level work? Is there a better solution?

    Thank you,
    Alex
    aboesteanu@yahoo.com Guest

  2. #2

    Default Re: preventing duplicate row insertion from asp.net app

    Jerry,
    Unortunately, I might not be able to modify the DB. (Besides, I do not
    see how the SPECIFIED business rule would be enforced through a
    primary key.)
    I would still need to know if there is a way to enforce the business
    rule at the app level.

    Thanks,
    Alex


    "Jerry III" <jerryiiihotmail.com> wrote in message news:<#b1kvL6XDHA.608TK2MSFTNGP12.phx.gbl>...
    > The only good way to do this is using a primary key on the table. Trying to
    > enforce data consistency in the application layer is not a good idea and
    > will generally not work (because sooner or later someone will forget to do
    > so, intentionally or not).
    >
    > Jerry
    aboesteanu@yahoo.com Guest

  3. #3

    Default Re: preventing duplicate row insertion from asp.net app

    You can have a primary key spanning more than one column (which would be the
    case of your specific rule).

    And yes, there is a way, you will simply check if the value exists before
    you try to insert it. There are multiple ways to do that and none will be
    100% fool proof, if you forget to do it once (or someone else working with
    the same data forgets to do it) or don't do it correctly (with regards to
    multiple requests executing simultaneously) you will end up with data that
    are "corrupted" based on your rules. You can do that checking with triggers,
    stored procedures, or plain SQL, it's entirely up to you...

    Jerry

    <aboesteanu> wrote in message
    news:34f0368e.0308110608.5be9e28bposting.google.c om...
    > Jerry,
    > Unortunately, I might not be able to modify the DB. (Besides, I do not
    > see how the SPECIFIED business rule would be enforced through a
    > primary key.)
    > I would still need to know if there is a way to enforce the business
    > rule at the app level.
    >
    > Thanks,
    > Alex
    >
    >
    > "Jerry III" <jerryiiihotmail.com> wrote in message
    news:<#b1kvL6XDHA.608TK2MSFTNGP12.phx.gbl>...
    > > The only good way to do this is using a primary key on the table. Trying
    to
    > > enforce data consistency in the application layer is not a good idea and
    > > will generally not work (because sooner or later someone will forget to
    do
    > > so, intentionally or not).
    > >
    > > Jerry

    Jerry III Guest

Similar Threads

  1. Ad Insertion
    By reefnet_alex in forum Macromedia Flash Flashcom
    Replies: 2
    Last Post: September 19th, 03:12 PM
  2. XML Bullet insertion
    By iaustin in forum Macromedia Flash Data Integration
    Replies: 2
    Last Post: July 7th, 09:34 PM
  3. Duplicate record insertion
    By force4ormore in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 19th, 11:00 AM
  4. Replies: 6
    Last Post: August 17th, 01:18 PM
  5. Preventing the double-insertion of records
    By Tim Chmielewski in forum ASP Database
    Replies: 1
    Last Post: June 3rd, 04:54 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