Benutzertabelle
CREATE TABLE `USERS` ( `ID` char(255) COLLATE utf8_unicode_ci NOT NULL, `NAME` char(255) COLLATE utf8_unicode_ci NOT NULL, `EMAIL` char(255) COLLATE utf8_unicode_ci DEFAULT NULL, `CREATED_IN` datetime NOT NULL, `SIGNED_IN` datetime NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Trigger
CREATE TRIGGER `ABC` BEFORE INSERT ON `USERS` FOR EACH ROW IF NEW.ID = "" OR NEW.NAME = "" OR NEW.CREATED_IN = "" OR NEW.CREATED_IN = "0000-00-00 00:00:00" OR NEW.SIGNED_IN = "" OR NEW.SIGNED_IN = "0000-00-00 00:00:00" THEN SIGNAL SQLSTATE "45000"; END IF
ID-Spalte
("", " ", " ", " ", 等等...) -> 应该返回错误("bf9 d 34 c9 08" = "bf9d34c908")Namensspalte
("", " ", " ", " ", 等等...) -> 应该返回错误E-Mail-Leiste
("", " ", " ", " ", 等等...) -> 应该返回错误("nkr owks lpehqp jmgdb @ gm ail.com" = "[电子邮件受保护]")Spalten CREATED_IN und SIGNED_IN
MySQL-Version ist 5.7
Wie können diese Anforderungen im BENUTZER-Formular am besten gehandhabt werden?
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
触发器可以如下所示：
CREATE TRIGGER process_input BEFORE INSERT ON USERS FOR EACH ROW BEGIN SET NEW.id = REPLACE(NEW.id, ' ', ''); IF NEW.id = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '`id` cannot be empty.'; END IF; SET NEW.name = TRIM(NEW.name); IF NEW.name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '`name` cannot be empty.'; END IF; SET NEW.email = REPLACE(NEW.email, ' ', ''); IF NEW.email = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '`email` cannot be empty.'; END IF; IF STR_TO_DATE(created_in, '%Y-%m-%d %H:%i:%s') IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Incorrect `created_in` datetime value.'; END IF; IF STR_TO_DATE(signed_in, '%Y-%m-%d %H:%i:%s') IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Incorrect `signed_in` datetime value.'; END IF; END注意 - 此触发器允许任何值为 NULL（但只有电子邮件可以为 NULL，所有其他列都定义为 NOT NULL）。
你可以重新排列这些块，并将概率最大的条件放在第一位。执行 SIGNAL 后触发器不会执行以下代码。