user table
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 column
("", " ", " ", " ", etc...) -> should return an error("bf9 d 34 c9 08" = "bf9d34c908")Name column
("", " ", " ", " ", etc...) -> should return an errorEmail Column
("", " ", " ", " ", etc...) -> should return an error("nkr owks lpehqp jmgdb @ gm ail.com" = "[email protected]")CREATED_IN and SIGNED_IN columns
MySQL version is 5.7
What is the best way to handle these requirements on the USERS table?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
A trigger can look like this:
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; ENDNOTE - This trigger allows any value to be NULL (but only email can be NULL, all other columns are defined as NOT NULL).
You can rearrange the blocks and put the condition with the highest probability first. The trigger will not execute the following code after executing SIGNAL.