MySQL 触发器问题：仅在更改列时触发？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4097949/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Trigger question: only trigger when a column is changed?
提问by kylex
I don't know if this is possible, but I have a column named activein a table. Whenever the active column gets changed, I would like to reset the date in the datecolumn, but ONLYif the activecolumn gets changed.
我不知道这是否可行，但我active在表中有一个列名。每当活动列更改时，我想重置date列中的日期，但前提是active列更改。
If other columns are changed but not the activecolumn, then the date would remain the same.
如果更改了其他列但未更改active列，则日期将保持不变。
回答by Haim Evgi
something like
就像是
DELIMITER //
CREATE TRIGGER updtrigger BEFORE UPDATE ON mytable
FOR EACH ROW
BEGIN
IF NEW.active <> OLD.active THEN
SET NEW.date = '';
END IF;
END
//
回答by John Glassman
Ran into an issue with the IF test in the #2 example. When one of the values is null the <> test returns null. This leads to the test not getting met and the action of the trigger will not get run even though the one value does not equal null at all. To fix this I came up with this test that uses <=> (NULL-safe equal). Hope this helps someone out.
在 #2 示例中遇到了 IF 测试的问题。当其中一个值为空时，<> 测试返回空。这会导致无法满足测试并且触发器的操作将不会运行，即使 one 值根本不等于 null。为了解决这个问题，我想出了这个使用 <=> (NULL-safe equal) 的测试。希望这可以帮助某人。
DELIMITER $$
DROP TRIGGER IF EXISTS updtrigger ;
$$
CREATE TRIGGER updtrigger AFTER UPDATE
ON yourTable FOR EACH ROW
BEGIN
IF ((NEW.active <=> OLD.active) = 0) THEN
SET NEW.date = '';
END IF;
$$
