mysql - 防止 MySQL 中的特定更新

Question

我的表格中有此列:

  `Status` VARCHAR(5) NULL DEFAULT 0 COMMENT 'DRAFT, SENT, or TRASH\nSENT cannot update to DRAFT\n' ,
    CONSTRAINT chk_Status CHECK (`Status` IN ('SENT', 'DRAFT', 'TRASH', NULL))

我想要做的是防止将 Status 为“SENT”的行更新为 Status 为“DRAFT”。

编辑:这是我想出的触发器，但从语法上看它似乎不正确。有什么建议吗？

TRIGGER update_Status
  BEFORE UPDATE ON `Status` WHERE `Status` = 'SENT'
  ON `EMAIL` FOR EACH ROW
  DENY UPDATE ON `Status` TO 'DRAFT',

Answer 1

试试这个:

DELIMITER /
CREATE TRIGGER update_status
BEFORE UPDATE ON Status 
FOR EACH ROW
BEGIN
  DECLARE wrong_status CONDITION FOR SQLSTATE '45123';
  IF new.status = 'DRAFT' AND old.Status = 'SENT' Then
     SIGNAL SQLSTATE '45123'
     SET MESSAGE_TEXT = 'Status cannot be changed from SENT to DRAFT';
  END IF;
END;
/
DELIMITER ;