Professional Web Applications Themes

Is this Code Reliable enough ? pls give comments..thanks - Microsoft SQL / MS SQL Server

Hi SQL Guru, I maintain stock availibility in a Table, by doing a checking in a Trigger. an Order Item should Not be inserted if stock is not enough. (DDL & Trigger attached below) In my Trigger code below , the questions are : 1) Can I always rely on ROWCOUNT to determine whether all Order Item has enough stock ?? 2) If any of order Item is Not Enough, should I RollBack before RaisError ?? Thanks in Advance, Krist CREATE TABLE StockAvailibility (prod_code CHAR(5) NOT NULL PRIMARY KEY, qty_on_hand INTEGER NOT NULL, qty_in_orders INTEGER NOT NULL); ALTER TRIGGER Trg_Order_Item ...

  1. #1

    Default Is this Code Reliable enough ? pls give comments..thanks

    Hi SQL Guru,

    I maintain stock availibility in a Table, by doing a checking in a
    Trigger. an Order Item should Not be inserted if stock is not enough.
    (DDL & Trigger attached below)

    In my Trigger code below , the questions are :

    1) Can I always rely on ROWCOUNT to determine whether all Order Item
    has enough stock ??

    2) If any of order Item is Not Enough, should I RollBack
    before RaisError ??

    Thanks in Advance,
    Krist

    CREATE TABLE StockAvailibility
    (prod_code CHAR(5) NOT NULL PRIMARY KEY,
    qty_on_hand INTEGER NOT NULL,
    qty_in_orders INTEGER NOT NULL);

    ALTER TRIGGER Trg_Order_Item
    ON OrderItem FOR INSERT AS
    UPDATE STK SET QTY_In_Order = QTY_In_Order+INS.QTY
    FROM StockAvailibility STK
    INNER JOIN INSERTED INS
    ON STK.Prod_Code = INS.Prod_Code
    WHERE Qty_On_Hand - ( QTY_In_Order + INS.QTY) >= 0
    IF RowCount <> ( Select COUNT(Distinct ProdCode) From INSERTED )
    BEGIN
    ROLLBACK TRAN
    RAISERROR('Stock is NOT enough ',16,1)
    RETURN
    END



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Krist Guest

  2. #2

    Default Re: Is this Code Reliable enough ? pls give comments..thanks

    By definition, I believe that you can rely on rowcount. However, I
    personally would not rely on it in this manner.

    From a brief examination of your logic, it looks like you have a fatal flaw
    in the update query. I assume that there is a one to many relationship
    between line items and stock_availability. In this case, your query will
    increase the quantity in order of a given stock by the value of an
    undetermined line item. You would rather have that number increased by the
    SUM of all line items for a given stock. In addition, the where clause is
    evaluated for each line item. The only rows removed by it are those where
    the line item quantity alone causes the problem. Try the following:

    update stockavailability set qty_in_orders = qty_in_orders + new.qty
    from (select prod_code, sum(qty) as qty from inserted group by prod_code) as
    new
    where new.prod_code = stockavailability.prod_code
    and qty_on_hand >= qty_in_orders + new.qty

    After the update, you should, imho, always check for errors before doing
    anything else. If you do this, then the checking for errors will cause
    rowcount to be reset. Therefore you need to save both to local variables
    before any checking occurs. Something like: select my_error = error,
    my_rows = rowcount

    Beyond that, there are other ways of doing this. You could create a
    constraint on the stockavailability table that enforces the rule. This
    would require no checking in your trigger at all but may not work based on
    your requirements. The reason for this is that you usually need to do this
    type of checking for updates as well.

    Lastly, I would do both - raiserror and rollback. I don't think the order
    is significant.

    "Krist Lioe" <com> wrote in message
    news:phx.gbl... 


    Scott Guest

  3. #3

    Default Re: Is this Code Reliable enough ? pls give comments..thanks

    Your UPDATE will only work if Proc_code is unique in OrderItem. The
    following works without that restriction.

    CREATE TRIGGER Trg_Order_Item ON OrderItem
    FOR INSERT
    AS
    UPDATE StockAvailability
    SET QTY_In_Order = QTY_In_Order +
    (SELECT SUM(qty)
    FROM Inserted
    WHERE prod_code = StockAvailability.prod_code)
    WHERE prod_code IN
    (SELECT prod_code
    FROM Inserted)
    IF EXISTS
    (SELECT *
    FROM StockAvailability
    WHERE Qty_On_Hand - QTY_In_Order <= 0)
    BEGIN
    ROLLBACK TRAN
    RAISERROR('Stock is NOT enough ',16,1)
    RETURN
    END

    But maybe you don't need the UPDATE at all. Drop the qty_in_order column and
    instead try creating a view to calculate the qty_in_order:

    CREATE VIEW StockAavailability_With_Order_Qty
    AS
    SELECT A.prod_code, A.qty_on_hand,
    COALESCE(SUM(O.qty),0) qty_in_order
    FROM StockAvailability AS A
    LEFT JOIN OrderItem AS O
    ON A.prod_code = O.prod_code
    GROUP BY A.prod_code, A.qty_on_hand

    Then you just need the trigger to validate each insert against this view.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

Similar Threads

  1. plz give me this code in C#
    By narula srinivas in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: October 18th, 05:01 AM
  2. Flash game, give comments?
    By Shinchi in forum Macromedia Flash Sitedesign
    Replies: 3
    Last Post: August 9th, 02: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