how to use join in trigger

I want to update another table on update of a table, with data fetching from third table on the basis of this updated colm value

Asked on January 24, 2020 in Mysql.
Add Comment
4 Answer(s)
DELIMITER $$

CREATE TRIGGER owed
    AFTER UPDATE ON bookings
    FOR EACH ROW
  BEGIN
    DECLARE dept VARCHAR(20);
    DECLARE rate DECIMAL(3, 2);
    IF NEW.odemeter_end <> OLD.odemeter_end THEN
      SELECT fm.department, vt.price
        INTO dept, rate
        FROM faculty_members fm, vehicles v, vehicle_types vt
        WHERE NEW.idMember = fm.idMember
          AND NEW.idVehicle = v.idVehicle
          AND v.type = vt.type;

      UPDATE departments
        SET owed_to_tfbs = (((NEW.odemeter_end - NEW.odemeter_start) * rate)
                           + owed_to_tfbs - NEW.fuel_purchased)
          WHERE department = dept;
    END IF;
  END;
$$

DELIMITER ;
Answered on January 24, 2020.
Add Comment

DELIMITER $$

CREATE TRIGGER add_timeline
AFTER UPDATE ON barcode_order_details
FOR EACH ROW
BEGIN
DECLARE time timestamp;
DECLARE user_id int(11);
DECLARE user_name varchar(300);
IF NEW.stage_5 = ‘complete’ THEN
SELECT uid,updated_ts
INTO user_id,time
FROM hub_item_order_internal_shipping
WHERE hioid = NEW.order_no
ORDER BY hoinsid DESC
LIMIT 0,1;

SELECT name
INTO user_name
FROM auser
WHERE uid = user_id;

INSERT INTO barcode_timeline (`barcode`,`current`,`user`) VALUES (NEW.bar_code_no, ‘Factory Dispatch’, user_name);
END IF;
END;
$$

DELIMITER ;

Answered on January 27, 2020.
Add Comment

DELIMITER $$

CREATE TRIGGER update_stage5
AFTER UPDATE ON hub_item_order
FOR EACH ROW
BEGIN
IF NEW.hiosid = 6 THEN
UPDATE barcode_order_details
SET `stage_5` = ‘complete’
WHERE `order_no` = NEW.`hioid`;
END IF;
END;
$$

DELIMITER ;

Answered on January 27, 2020.
Add Comment

DELIMITER $$

CREATE TRIGGER insert_extras
AFTER INSERT ON `barcode_order_details`
FOR EACH ROW
BEGIN
DECLARE type int(11);
DECLARE hub_id int(11);
DECLARE hcode varchar(300);

SELECT hstid,hid
INTO type,hub_id
FROM hub_item_order
WHERE hioid = NEW.order_no;

SELECT hub_code
INTO hcode
FROM hub
WHERE hid = hub_id;

INSERT INTO barcode_extras (`barcode_ref`,`dtype`,`hub_code`) VALUES (NEW.bar_code_no, type, hcode);
END;
$$

DELIMITER ;

Answered on February 3, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.