SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    TRIGGER: Update Parent Table when Child is updated...

    I'm fairly new to MySQL in general... I've managed to perform INSERTs via PHP... But now I want to make life easier for myself and use Triggers (or associated functionality)...

    I understand the very basic of Triggers such as adding two columns before insert for example... What I want now is slightly mroe complex (but only slightly)...

    Can someone please show me the basic trigger to achieve the following:

    tblBasket = BasketID, BasketValue
    tblBasketItem = ItemID, BasketID, ItemName, ItemValue

    Each time an item is inserted into 'tblBasketItem' I want the value of all TblBasketItem's with the same BasketID to be TOTALd and the parent tblbasket.BasketValue UPDATEd...

    Any and all help gratefull recieved..

    Baz

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that's not possible with mysql's current incarantion of triggers. a trigger in mysql can only affect the row being inserted/updated/deleted and can not reference/insert/update/delete records in other tables.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would it be possible for for that INSERT/UPDATE trigger to call a PROCEDURE/FUNCTION that TOTALs the tblBasketItems and Updates the tblBasket parent???

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    nope. but instead of a trigger, you could write a stored procedure that does both the insert in to the basket items and updates the total items in the basket.

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, in that case, my question would be.. How? I've no experience whatsoever with stored procedures...

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok.. i've got two tables
    ------------------------------------------------------------
    tblBasket - BasketID, ValueExVAT, ValueIncVAT
    tblBasketItem - BItemID, BasketID, UnitPriceEx, UnitPriceInc, SubPriceEx, SubpriceInc, Quantity
    ------------------------------------------------------------
    I have a trigger (tr_tblbasketitem_au) (AFTER UPDATE)
    BEGIN
    CALL pr_tblbasketitem_update(NEW.bitemid, NEW.basketid);
    CALL pr_tblbasket_update(NEW.basketid);
    END
    ------------------------------------------------------------
    Ignoreing the second procedure, which at the moment is ok...
    The First procedure (pr_tblbasketitem_update):

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `companymanagementmysql`.`pr_tblbasketitem_update` $$
    CREATE PROCEDURE `pr_tblbasketitem_update`(varItemID INT, varBasketID INT)
    BEGIN
    DECLARE varRowNum INT;
    SET varRowNum = (SELECT MAX(ItemNumber) from tblbasketitem where basketid = varBasketID);
    UPDATE tblbasketitem SET subpriceex = unitpriceex * quantity, subpriceinc = unitpriceinc * quantity, itemnumber = (varRowNum + 1) where BItemID = varItemID;
    END $$

    DELIMITER ;
    ----------------------------------------------------------------
    But when i update data in tblBasketItem, i get the following error message:

    Can't update table 'tblbasketitem' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
    -----------------------------------------------------------------
    I understand the ENGLISH, but i don't understand how it is possible... I've just updated a row in tblBasketItem, it's the AFTER UPDATE event, so it's finished updating it... And theres only one update operation on that table...

    Can anyone shed any light?

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, ignore post #2. apparently mysql's latest version of mysql 5 does allow for triggers to reference rows and tables outside of the context of the triggered row.

    that being said, i don't understand what you are doing with the procedure pr_tblbasketitem_update. why are you updating the basket item table if your origianl goal was to update the quantities of the basket?

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My aim is that via PHP the basics will be entered into tblBasketItem as a user selects a product on the website

    BasketID - Link to Parent tblBasket
    ProductID - Link to tblProduct
    Quantity - Number of specified product...

    Once the item has been added, I wish the subpriceinc and subpriceex to be calculated (unitpriceex * qty = subpriceex) and the itemnumber to be calculated (1, 2, 3, 4, 5 etc...)... There could also be additional fields required at a later date...

    The second (working) function will then total the products in the specified basket and update the BasketValue etc...

    I'm doing it this way to keep the amount of information being sent back and forth as little as possible...

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that only thing i can suggest is moving that code in to the trigger and making it a before update. but i must also suggest that using mysql to number the items in your basket is a bad idea. why do you need to do that?

  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How else would you suggest that I autonumber each individual basket item... It's not really an essential part of the project, but it's good to have a row number against an item...

    But do you know what the Procedure was unable to update the table???


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
  •