Results 1 to 6 of 6

Thread: Trigger update

  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: Trigger update

    Hi

    I have come up with this

    ALTER TRIGGER Trigger6
    ON dbo.ReceiveOrder
    FOR UPDATE
    AS
    UPDATE Consumables
    SET Quantity = Consumables.Quantity + ReceiveOrder.Quantity_Received
    FROM Consumables INNER JOIN
    ReceiveOrder ON Consumables.Product = ReceiveOrder.Product



    This almost does what I want. I have Quantity_Received=25, Quantity=100 but when it triggers I get the new Quantity=150 any ideas.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The instruction in your trigger is to update each and every Consumables record that can be related to a ReceiveOrder record.

    What the instruction should have been, is to UPDATE only those Consumables records that are related to the ReceiveOrder record(s) that fired the trigger.
    Within the body of the trigger definition, the ReceiveOrder record(s) that fired the trigger can be found in the table Inserted.

    When you write a trigger, write it in such a way that it will also work in a situation where multiple records were affected by one triggering event (like 1 UPDATE statement that leads to an update of 1000 records, will fire the trigger only once and the Inserted table will contain 1000 records.).
    Code:
    CREATE TRIGGER Trigger16
    ON dbo.ReceiveOrder
    FOR UPDATE
    AS
    UPDATE U
    SET Quantity = U.Quantity + Inserted.Quantity_Received
    FROM Consumables as U 
    	INNER JOIN Inserted ON 
    		U.Product = Inserted.Product
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Are you sure you want to use a trigger to do that calculation? Have you thought about the consequences of doing this with a trigger? How about testing it? What will you have to do in case some system misbehaves, can you correct your tables to the previous correct situation?

    When one UPDATES one ReceiveOrder record multiple times, with always the same value, lets say 10, the trigger will fire each and every time, each time increasing Quantity. At the end you will find Quantity with a huge value and a value in Quantity_Received of only 10. You will have no way to tell where that huge Quantity came from. There is no way to tell the difference between a column in a record that holds a 10 at the beginning and after 1 million UPDATEs with the very same value 10.

    Triggers work perfect in a perfect world. I have learned that the companies I work(ed) for are not located in perfect worlds.

    Unless you are doing this to learn about triggers, I strongly advise you to reconsider your design.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jun 2011
    Posts
    5
    Hi

    I am new to this so forgive me. What I have is a table of consumables each having a location and a quantity. When an order is recieved the quantity received updates the particular consumable's quantity.

    I tried the code you supplied but it still adds double what was received.

    If not using a trigger what would you suggest?

    Thanks, James
    Last edited by bobred; 06-22-11 at 11:21.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Post

    I tried the code you supplied but it still adds double what was received.
    As I wrote before:
    There is no way to tell the difference between a column in a record that holds a 10 at the beginning and after 1 million UPDATEs with the very same value 10.
    In your case I don't think your trigger gets fired 1 million times, but twice, and that somehow the UPDATE statement is executed twice, perhaps due to the update of another column.

    As I wrote before (I know I start to sound like a real jerk):
    Triggers work perfect in a perfect world. I have learned that the companies I work(ed) for are not located in perfect worlds.
    If not using a trigger what would you suggest?
    I don't know your system, so the best person who can answer this is yourself. But in any case, you should get rid of updating Quantity based on an UPDATE in ReceiveOrder. New Orders should be entered in ReceiveOrder by an INSERT, not by an UPDATE. And add a DATETIME column to ReceiveOrder so you start storing historic data.
    To get your current stock, you could then use:
    Code:
    SELECT SUM(Quantity_Received) - SUM(Quantity_Delivered)
    FROM ReceiveOrder 
         INNER JOIN DeliverOrder ON
                   ReceiveOrder.Product = DeliverOrder.Product
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jun 2011
    Posts
    5
    Hi

    It works now, I realised I had another trigger on one of the columns, I have changed this to a computed column and everything is fine, well for now.

    Thanks for your help. As I said I'm learning so I'm sure there are better ways to do things.

    James

Posting Permissions

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