Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    3

    Unanswered: create trigger that prevent order being processed if the amount is 0 in storage

    When i process a order in table Orders_t i would like to check in storage_t if we defenetly have it in storage. .... if we have it in storage, i decrease the "amount" by 1 ..(amount -1), and process the order. Otherwise it will return nothing.

    This is what i´ve come up with so far:


    PHP Code:
    CREATE TRIGGER checkInStorage
    ON orders_t
    FOR INSERTUPDATE

    AS
    DECLARE 
     @
    tOrderId char(3),
    BEGIN
      SET 
    @tOrderId = (SELECT orderId FROM INSERTED)

    --
    check if the amount in storage

    IF EXIST(SELECT amount FROM storage_t WHERE orderId = @tOrderId and amount >= 0)
    BEGIN --if it return truei update the storage by decrease the amount with one
      UPDATE storage_t
      SET 
    (amount amount 1)
      
    WHERE orderId = @tOrderId
    END 


    this doesn´t work...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    When you "process" an order, just decrement it then
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2006
    Posts
    3
    but when the amount is 0?

    Should i decrement it?

    Can´t process a order that i don´t have in storage

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is right on the hairy edge of what is appropriate for a trigger, so have you considered placing this verification logic (and perhaps the decrementing as well) in the stored procedure you call to process the order? You are using a stored procedure, right?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2006
    Posts
    3
    no trying to do this in a trigger

    thank you for fast reply, now i want some "sql suggestions"

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by gauti
    no trying to do this in a trigger.
    Maybe I missed something, but it sure looks to me like you are trying to do this in a trigger. I agree with Blindman, this almost certainly should not be done in a trigger: there are only a few VERY specialized circumstances where that would make sense. This smells like a disaster in search of its next victim to me!

    -PatP

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by gauti
    no trying to do this in a trigger

    thank you for fast reply, now i want some "sql suggestions"
    SQL suggestions:

    1. This is not appropriate in a trigger.
    2. Put it in a stored proc to handle the "order process" using a transaction for ACID purposes.
    3. Decrement the amoount column. If the new value is < o insert an order line into your VendorResupplyOrder table and flag the item as back-ordered. Close the transaction.

    -- This is all just a Figment of my Imagination --

Posting Permissions

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