Попробуйте это:
DELIMITER $$
CREATE TRIGGER `trg_part_upd` AFTER UPDATE ON `tbl_sub_model_eng_trans_part`
FOR EACH ROW BEGIN
IF NEW.engine_sk = 0 AND NEW.trans_sk = 0
THEN
UPDATE tbl_sub_model tsm
INNER JOIN tbl_fct_sub_eng_trans tfset ON tsm.sub_model_sk = tfset.sub_model_sk
INNER JOIN tbl_sub_model_eng_trans_part tsmetp ON tfset.trans_sk = tsmetp.trans_sk
SET tsm.last_modified_date = NOW()
WHERE tsmetp.sub_model_sk=NEW.sub_model_sk;
END IF;
END;
$$
DELIMITER ;