MySQL 触发器用法：如果列为空，则设置其他列为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21551150/
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 usage : if column is null set other column null
提问by Jbartmann
How do I realize the following in MySQL with Triggers:
When value of some column is null -> set other column values to null
and
when value of some column is not null -> set other column values to null
我如何使用触发器在 MySQL 中实现以下内容：当某些列的值为空时 -> 将其他列值设置为空，
并且当某列的值不为空时 -> 将其他列值设置为空
table definition:
表定义：
CREATE TABLE variations (
id int(10) NOT NULL,
x1 int(10) NOT NULL,
x2 int(10),
x1_option1 BOOL,
x2_option1 BOOL,
x1_option2 varchar(10),
x2_option2 varchar(10)
);
The idea is that we have 2 Elements, x1and x2. While x1is mandatory, x2is optional and can be null. Both, x1 and x2 have two options: x1_option1, x2_option1, x1_option2and x2_option2.
The first rule should be that when x2 is null, both options for x2 (x2_option1, x2_option2) must also be null.
My attempt:
这个想法是我们有 2 个元素，x1并且x2. Whilex1是强制性的，x2是可选的，可以为空。无论X1和X2有两种选择：x1_option1，x2_option1，x1_option2和x2_option2。
第一条规则应该是当 x2 为 null 时，x2 ( x2_option1, x2_option2) 的两个选项也必须为 null。
我的尝试：
CREATE
TRIGGER check_null_x2 BEFORE INSERT
ON variations
FOR EACH ROW BEGIN
IF NEW.x2 IS NULL THEN
SET NEW.x2_option1 = NULL;
SET NEW.x2_option2 = NULL;
END IF;
END$$
Throws Error:
抛出错误：
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
Can you please help me figuring out whats wrong? I just dont understand what '' means.
你能帮我弄清楚出了什么问题吗？我只是不明白''是什么意思。
The second rule should be that there can only be one of the two options selected. that means if x2_option1is NOT NULL, x2_options2must be NULL. In general i think this can be done the same way as the first rule. My question: how can i do multiple 'IF', 'ELSE IF' etc in one trigger?
第二条规则应该是只能选择两个选项之一。这意味着如果x2_option1不是 NULL，则x2_options2必须为 NULL。一般来说，我认为这可以与第一条规则相同的方式完成。我的问题：如何在一个触发器中执行多个“IF”、“ELSE IF”等操作？
回答by jainvikram444
This is syntax for trigger:
这是触发器的语法：
delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;//
delimiter ;
...and your code is here:
...你的代码在这里：
DELIMITER //
CREATE TRIGGER check_null_x2 BEFORE INSERT ON variations
FOR EACH ROW
BEGIN
IF NEW.x2 IS NULL THEN
SET NEW.x2_option1 = NULL;
SET NEW.x2_option2 = NULL;
END IF;
END$$ -- THIS LINE SHOULD BE: "END;//"
DELIMITER ;
回答by Volkan Ulukut
you seem to have ";" set as DELIMETER, which causes the query to execute once it sees a ";". try changing it first:
你似乎有“;” 设置为 DELIMETER，这会导致查询在看到“;”后执行。先尝试改变它：
DELIMITER //
CREATE
TRIGGER check_null_x2 BEFORE INSERT
ON variations
FOR EACH ROW BEGIN
IF NEW.x2 IS NULL THEN
SET NEW.x2_option1 = NULL;
SET NEW.x2_option2 = NULL;
END IF;
END;//
DELIMITER ;
回答by Raghunandan Krishnamurthy
CREATE TRIGGER `XXXXXX` BEFORE INSERT ON `XXXXXXXXXXX` FOR EACH ROW BEGIN
IF ( NEW.aaaaaa IS NULL ) THEN
SET NEW.XXXXXX = NULL;
SET NEW.YYYYYYYYY = NULL;
END IF;
END
Worked for me....
