如果表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;