mysql 触发器检查字段

触发器语法

create trigger (名字) [before|after] [insert|update|delete] on (表名) for each row
begin
(内容)
end

1. 处理时间，写入数据之前 before，写入数据之后 after
2. 操作动作，insert 插入，update 更新， delete 删除
3. for each row 逐行检查
4. 如果中间有 signal 报错，则操作终止

插入的数据

• new

1. 比如你插入一列 device_ip 1.1.1.1, age 12, state 0
2. 在触发器获取就是，new.device_ip 就是插入的IP

IF / Switch Case

• IF

	IF(new.id = 6) -- 当新id为6时
	THEN IF(new.et_name = '6') -- 当新id为6，并且name也为6才执行插入语句
		THEN INSERT INTO bysj_dt VALUES (new.id,new.et_name);
		END IF;
	END IF;

• switch case

CASE 
	WHEN new.id = 6 AND new.et_name = '6' THEN
		INSERT INTO bysj_dt VALUES (new.id,'等于6');
	WHEN new.id < 6 THEN
		INSERT INTO bysj_dt VALUES (new.id,'小于6');
	ELSE
		INSERT INTO bysj_dt VALUES (100,'100');
END CASE;

使用变量

declare data varchar(255);
select mip into data from sno_net_device where mip = new.device_ip;
if data = "1.1.1.1" then
    insert Into sth_table values (1, "2");
end if;

抛出异常

signal sqlstate "Road1123" set message_text = '错误信息提示';

插入之前做检查，不通过则报错

1. 检查字段是否存在另一张表中

CREATE TRIGGER `ip限制` BEFORE INSERT ON `backup_config_control` FOR EACH ROW begin
declare data varchar(255);
select mip into data from sno_net_device where mip = new.device_ip;
IF data is null then
 signal sqlstate "HY000" set message_text = 'mip查找失败，请检查MIP是否存在';
end if;
end

2. 检查字段是否符合IP格式

CREATE TRIGGER `ip限制` BEFORE INSERT ON `backup_config_control` FOR EACH ROW begin
IF not new.device_ip Regexp '^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}$'then
 signal sqlstate "HY000" set message_text = 'device_ip 必须为 IP格式！';
end if;
end

参考

• https://blog.csdn.net/good_good_xiu/article/details/115631369