Jadual pengguna
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
Pencetus
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
ruangan ID
("", " ", " ", " ", 等等...) -> 应该返回错误("bf9 d 34 c9 08" = "bf9d34c908")Lajur nama
("", " ", " ", " ", 等等...) -> 应该返回错误Bar E-mel
("", " ", " ", " ", 等等...) -> 应该返回错误("nkr owks lpehqp jmgdb @ gm ail.com" = "[电子邮件受保护]")CREATED_IN dan SIGNED_IN
Versi MySQL ialah 5.7
Apakah cara terbaik untuk mengendalikan keperluan ini pada borang PENGGUNA?
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Pencetus boleh kelihatan seperti ini:
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; ENDNOTA - Pencetus ini membenarkan sebarang nilai menjadi NULL (tetapi hanya e-mel boleh menjadi NULL, semua lajur lain ditakrifkan sebagai BUKAN NULL).
Anda boleh menyusun semula blok dan meletakkan keadaan dengan kebarangkalian yang paling tinggi dahulu. Pencetus tidak akan melaksanakan kod berikut selepas melaksanakan SIGNAL.