mysql - 引发错误在 MySQL 触发器中不起作用
问题描述
如果一个人的 lastname 不是全大写或者他们的 firstname 的第一个字母不是 caps ,我试图在我的触发器中引发一个错误。但是,如果遇到这些情况,我的代码似乎不会发出警报。有人可以向我解释我该怎么做吗?
这是我的桌子:
DROP TABLE IF EXISTS people;
CREATE TABLE IF NOT EXISTS people (
Id_People smallint(5) unsigned NOT NULL AUTO_INCREMENT,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Birth DATE,
PRIMARY KEY (Id_People)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1002 ;DROP TRIGGER IF EXISTS insert_people;
这是我的触发器
DROP TRIGGER IF EXISTS insert_people;
DELIMITER $$
CREATE TRIGGER insert_people BEFORE INSERT ON people
FOR EACH ROW BEGIN
IF NOT (NEW.Lastname like UCASE ) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'the lastname must be in full caps ';
ELSE IF
NOT (NEW.Firstname like ucfirst ) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'the first letter of the firstname must be in caps';
END IF;
END IF;
END$$
解决方案
你的代码发现那些是不正确的。
请添加DELIMITER
到触发代码,因为在示例站点中不需要
DROP TABLE IF EXISTS people; CREATE TABLE IF NOT EXISTS people ( Id_People smallint(5) unsigned NOT NULL AUTO_INCREMENT, Firstname varchar(20) NOT NULL, Lastname varchar(20) NOT NULL, Birth DATE, PRIMARY KEY (Id_People) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1002 ;DROP TRIGGER IF EXISTS insert_people;
CREATE TRIGGER insert_people BEFORE INSERT ON people FOR EACH ROW BEGIN IF NOT (NEW.Lastname=BINARY UPPER(NEW.Lastname)) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'the lastname must be in full caps '; ELSE IF NOT (ASCII(NEW.Firstname) BETWEEN 65 AND 90 ) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'the first letter of the firstname must be in caps'; END IF; END IF; END
INSERT INTO people VALUES (NULL,'Hallo','Kitty','1980-01-01')
姓氏必须大写
INSERT INTO people VALUES (NULL,'hallo','KITTY','1980-01-01')
名字的第一个字母必须大写
db<>在这里摆弄
推荐阅读
- typescript - Webpack ts-loader`找不到模块:错误:无法解析`
- angular - 无法读取 NgbTypeahead 中未定义的属性“管道”
- python - 从二进制文件读取的不同行为
- python - 使用 pypyodbc 对列名进行 SQL Server 编码
- python - Coco 类型数据集的评估返回错误
- c# - 将 FileSystemWatcher 与具有 FILE_ATTRIBUTE_RECALL_ON_DATA_ACCESS 的文件一起使用
- python - Python append 从列表中删除元素
- java - 在java中查找多个日期间隔之间的差异
- vue.js - nuxt 在点击 this.$auth.fetchUser() 后获取用户名
- datatable - 从 SLT 表中删除部分数据