Professional Web Applications Themes

Is THIS Trigger Correct under CASCADE DELETE ? - Microsoft SQL / MS SQL Server

Hi SQL Gurus, I have an order processing application where the Order Item must be REJECTED If the stock is NOT Available. I have a Stock Table that interactively updated during data entry. CREATE TABLE MSSTK -- Table for Stock Availibility (PRODCode CHAR(5) NOT NULL Primary Key, QTYIN INT, QTYOUT) I am using CASCADE DELETE in the Foreign Key from Order Detail to Order Header. Below is the TRIGGER For ORDER DETAIL. The question is : Is this the correct way making sure if the Order Item is only INSERTED if the Stock Available ?? ALTER TRIGGER TD_T02QUOD ON T02QUOD ...

  1. #1

    Default Is THIS Trigger Correct under CASCADE DELETE ?

    Hi SQL Gurus,
    I have an order processing application where the Order Item must be
    REJECTED If the stock is NOT Available.
    I have a Stock Table that interactively updated during data entry.
    CREATE TABLE MSSTK -- Table for Stock Availibility
    (PRODCode CHAR(5) NOT NULL Primary Key,
    QTYIN INT,
    QTYOUT)

    I am using CASCADE DELETE in the Foreign Key from Order Detail to Order
    Header. Below is the TRIGGER For ORDER DETAIL.
    The question is : Is this the correct way making sure if the Order Item
    is only INSERTED if the Stock Available ??

    ALTER TRIGGER TD_T02QUOD
    ON T02QUOD FOR DELETE AS
    UPDATE STK SET QTYOUT = QTYOUT-DEL.QTY
    FROM MSSTK STK
    INNER JOIN (
    SELECT ProdCode, ISNULL(SUM(Kuantum),0) QTY FROM
    DELETED GROUP BY ProdCode) DEL
    ON STK.ProdCode = Del.ProdCode
    WHERE QTYOUT - DEL.QTY >= 0
    IF RowCount <> ( Select COUNT(Distinct ProdCode) From DELETED )
    BEGIN
    RAISERROR('Stock is NOT enough !',16,1)
    RETURN
    END

    Thanks in advance,
    Krist










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

  2. #2

    Default Is THIS Trigger Correct under CASCADE DELETE ?

    I'm a great fan of writing test scripts if I'm not sure of
    something. Look at these to see where you may have
    problems. In the first (like your code) you would have to
    handle the error to roll back the transaction.

    drop table a
    go
    create table a (i int, j int)
    go
    create trigger tr on a for update
    as
    update a set j = j + 20
    raiserror('fail',16,1)
    go

    insert a select 1,1

    update a set i = i + 1
    select * from a
    i j
    ----------- -----------
    2 21

    drop table a
    go
    create table a (i int, j int)
    go
    create trigger tr on a for update
    as
    update a set j = j + 20
    rollback tran
    raiserror('fail',16,1)
    go
    insert a select 1,1

    update a set i = i + 1
    select * from a
    i j
    ----------- -----------
    1 1


     
    Item must be 
    data entry. 
    Detail to Order 
    the Order Item 
    DELETED ) 
    *** 
    Nigel Guest

  3. #3

    Default Re: Is THIS Trigger Correct under CASCADE DELETE ?

    >> Is this the correct way making sure if the Order Item
    is only INSERTED if the Stock Available ? <<

    It is one way, which is highly proprietary, not portable and not too
    flexible.

    I am not sure just what your tables mean (but thanks for the DDL!!) I am
    guessing that the columns tell you the quantity on hand and the total
    quantity committed to filling orders today (or whenever you adjust stock
    levels). Later, another procedure pulls the stock, builds the orders,
    checks shrinkage and spoilage and reduces quantity on hand.

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

    The constraint would be violated if you tried to update qty_in_orders
    below quantity on hand. You just have to catch the error and take
    actions in the front end -- reject the order, ask for smaller quantity,
    call a routine that offers substitutes, etc. A trigger makes a decision
    and that is the end of it; your front end has to live with it.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

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

Similar Threads

  1. possible to DELETE CASCADE?
    By Miles Keaton in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: December 30th, 10:42 PM
  2. A "cascade on delete" constraints deletes AFTER the source is gone??
    By Vitaly Belman in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: December 20th, 05:36 PM
  3. Perform cascade delete
    By basidati in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 7th, 09:53 PM

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