Post reply

Help with trigger

  • July 5, 2011 at 2:33 am

    #240858

    Hi, I'm new to sql, so sorry for my ignorance.

    I have 2 tables, when the table that q1 is updated the trigger is fired and should take the value of q1 and add to Consumables.Quantity.

    UPDATE U SET Quantity = U.Quantity + Inserted.q1 FROM Consumables as U INNER JOIN Inserted ON U.Product = Inserted.Product

    This I'm sure used to work, but no longer

    Any ideas?

    James

  • July 5, 2011 at 2:40 am

    #1348105

    First you don't need the As, second try this

    UPDATE Consumables SET Quantity = U.Quantity + Inserted.q1 FROM Consumables U INNER JOIN Inserted ON U.Product = Inserted.Product

  • July 5, 2011 at 2:46 am

    #1348107

    Hi,Thanks

    Tried it but it didn't work.

    James

  • July 5, 2011 at 2:49 am

    #1348108

    Yeah, I'm doing some tests on your problem.

    And that didn't work, my excuse for this, I'm running a few tests on, it now.

    First is the trigger on Inserted or on Consumables.

  • July 5, 2011 at 2:55 am

    #1348111

    james.murphy (7/5/2011)

    This I'm sure used to work, but no longer

    James, please will you elaborate on this? Are there any error messages, for example?

    Also, using UPDATE FROM will give unexpected results when Inserted has more than one row. Which value of q1 do you need to be used when inserting/updating more than one row?

    John

  • July 5, 2011 at 3:01 am

    #1348113

    We also need to know on which table you placed the trigger, I did a quick test and when I place the

    UPDATE Consumables SET Quantity = U.Quantity + I.q1 FROM Consumables U INNER JOIN Inserted I ON U.Product = I.Product

    trigger on Consumables it doesn't recognise the I.q1 column, but if I do that on Inserted its fine

  • July 5, 2011 at 3:07 am

    #1348119

    No errors.

    The trigger is on the table ReceiveOrder that has q1 on it.

    James

  • July 5, 2011 at 3:15 am

    #1348126

    So the trigger looks like this

    CREATE TRIGGER trig_Consumables

    ON ReceiveOrder

    FOR UPDATE

    AS

    BEGIN

    UPDATE Consumables SET Quantity = U.Quantity + I.q1 FROM Consumables U INNER JOIN Inserted I ON U.Product = I.Product

    END

    It will be created successfully but it will give a warning that I.q1 and I.Product are invalid.

    I create a table ReceiveOrder, inserted a record and then updated it.

    It resulted the quantity in Consumables to be updated with the q1 and product from the ReceiveOrder instead of the inserted table

  • July 5, 2011 at 3:20 am

    #1348130

    Yes the trigger looks like that.

  • July 5, 2011 at 3:43 am

    #1348145

    Hi, here is the structure of the two tables, they seem ok

  • July 5, 2011 at 3:45 am

    #1348147

    So where does Inserted fit into the picture

  • July 5, 2011 at 4:00 am

    #1348153

    I thought this was the inserted table

  • July 5, 2011 at 4:02 am

    #1348156

    Ah ok

    so there is no inserted table.

  • July 5, 2011 at 4:12 am

    #1348161

    No, it is some code I found, which did work at one point???

  • July 5, 2011 at 4:13 am

    #1348162

    inserted and deleted are transition table created when executing a trigger

    http://msdn.microsoft.com/en-us/library/ms191300.aspx

    Jayanth Kurup[/url]

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply