插入值检查前的 MySQL 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12836270/
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 before Insert value Checking
提问by Bitmap
I have a table staffwith officecolumn. Currently the officecolumn do not accept NULL values. The application persisting onto this table has a bug which meant that, when the staff has not been assigned an office, it tries inserting a NULL value onto the table.
我有一个staff带office列的表。目前该office列不接受 NULL 值。持久保存在该表上的应用程序有一个错误，这意味着当员工没有被分配到办公室时，它会尝试在表中插入一个 NULL 值。
I have been asked to used a trigger to intercept the insert onto the Stafftable and check if the officevalue is NULL and replace it with value N/A.
我被要求使用触发器来拦截Staff表上的插入并检查office值是否为 NULL 并将其替换为 value N/A。
Below is my attempt so far, but do have errorin attempt to implement. Any Ideas on how to resolve this.
以下是我迄今为止的尝试，但确实有error尝试实施。关于如何解决这个问题的任何想法。
CREATE TRIGGER staffOfficeNullReplacerTrigger BEFORE INSERT ON Staff
FOR EACH ROW BEGIN
IF (NEW.office IS NULL)
INSERT INTO Staff SET office="N/A";
END IF
END;
The error:
错误：
MySQL Database Error: 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 'INSERT INTO Staff SET office="N/A"; END'
MySQL 数据库错误：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在“INSERT INTO Staff SET office="N/A”附近使用的正确语法；结尾'
回答by unutbu
First, alter the table to allow NULLs:
首先，更改表以允许 NULL：
ALTER TABLE Staff MODIFY office CHAR(40) DEFAULT "N/A";
(Change CHAR(40)to whatever is appropriate.) Then you could use as your trigger:
（更改CHAR(40)为任何合适的内容。）然后您可以将其用作触发器：
CREATE TRIGGER staffOfficeNullReplacerTrigger
BEFORE INSERT
ON Staff
FOR EACH ROW BEGIN
IF (NEW.office IS NULL) THEN
SET NEW.office = "N/A";
END IF
回答by galaxyAbstractor
Shouldn't it be something like this:
不应该是这样的：
DELIMITER $$
CREATE TRIGGER staffOfficeNullReplacerTrigger BEFORE INSERT ON Staff
FOR EACH ROW BEGIN
IF (NEW.office IS NULL) THEN
INSERT INTO Staff(office) VALUES("N/A");
END IF;
END$$
回答by DEVEX
CREATE TRIGGER staffOfficeNullReplacerTrigger BEFORE INSERT ON Staff
FOR EACH ROW BEGIN
IF (NEW.office IS NULL)
INSERT INTO Staff SET office="N/A";
END IF
; add semi colon after END IF
; 在 END IF 后添加分号
END;
