如果表LandContract的更新意味着字段LandContract.PriceType的值从“ Fastårspris”更改为“Rörligtårspris”,我想从表LandContractAnnualPrice中删除​​,其中LandContractAnnualPrice.LandContractId = LandContractId.Id

我知道如何在PHP中执行此操作,但是最好将这种逻辑放在MySQL触发器中吗?如果是这样,那么我应该如何定义这样的触发器?

以下是我尝试创建触发器的尝试。这是我第一次写一个,所以我不敢尝试:)它会做我想要的吗?

DELIMITER $$

CREATE TRIGGER delete_from_related_table AFTER UPDATE
 ON LandContract
 FOR EACH ROW
 BEGIN
  IF (OLD.PriceType = 'Rörligt årspris' AND NEW.PriceType = 'Fast årspris') THEN
   DELETE FROM LandContractAnnualPrice WHERE LandContractId = OLD.Id;
   END IF;
 END$$

DELIMITER ;

最佳答案

尝试这个 :

CREATE OR REPLACE TRIGGER MYTRIGGER
AFTER UPDATE ON LandContract
REFERENCING NEW AS newRow OLD AS oldRow
FOR EACH ROW
 BEGIN
  IF (oldRow.PriceType = 'Rörligt årspris' AND newRow .PriceType = 'Fast årspris') THEN
     DELETE FROM LandContractAnnualPrice WHERE LandContractId = oldRow.Id;
  END IF;
END MYTRIGGER;

09-07 10:04