CODEWITHSUNDEEP
mysqltriggers
Newbie
Newbie

Reputation: 249

Trigger to update another table while inserting in the current table using MySql?

I'm not good at triggers in databases

Trigger: 

delimiter $$

    CREATE
    DEFINER=`root`@`localhost`
    TRIGGER `bon_apetite`.`orders_insert_update_trigger`
    AFTER INSERT ON `bon_apetite`.`orders`
    FOR EACH ROW
    BEGIN
    update budget_tracking set new.monthly_balance = old.monthly_balance + new.total_price
    where tracking_userid = new.order_userid;
    END
    $$

tables:

budget_tracking and orders

columns:

budget_tracking (id,budget_userid,monthly_balance,created_date,modified_date)

orders(order_id,order_userid,total_price,created_date,modified_date)

budget_userid and order_userid are foriegn keys to a primary key in another table.

All I want is when I'm inserting values to orders table a trigger should update the monthly_balance field of budget_tracking table along with created_date and modified_date of both the tables.

Any help will be appreciated!

Upvotes: 0

Views: 1670

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` TRIGGER `bon_apetite`.`orders_insert_update_trigger` AFTER INSERT ON `bon_apetite`.`orders` FOR EACH ROW
BEGIN
UPDATE budget_tracking SET monthly_balance = monthly_balance + new.total_price
WHERE tracking_userid = new.order_userid;
END$$

DELIMITER ;

Upvotes: 1

aykut
aykut

Reputation: 3094

Try this

BEGIN
update budget_tracking set monthly_balance = monthly_balance + new.total_price
where tracking_userid = new.order_userid;
END

Upvotes: 1

Related Questions