-- 见表语句
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `zj_cus_column`;
CREATE TABLE `zj_cus_column` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`cu_id` int(11) DEFAULT NULL COMMENT '客户id',
`table_name` varchar(32) DEFAULT NULL COMMENT '表名',
`col_name` varchar(32) DEFAULT NULL COMMENT '列名',
`isdelete` tinyint(4) DEFAULT '0' COMMENT '是否删除',
`field_id` int(11) DEFAULT NULL COMMENT '字段id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COMMENT='自定义列';
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `zj_field_status`;
CREATE TABLE `zj_field_status` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(32) DEFAULT NULL COMMENT '字段名称',
`status` tinyint(4) DEFAULT '0' COMMENT '状态',
`type` tinyint(4) DEFAULT '0' COMMENT '字段类型',
`table_name` varchar(32) DEFAULT NULL COMMENT '表名',
`content` varchar(128) DEFAULT NULL COMMENT '字段内容',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`is_delete` tinyint(4) DEFAULT '0' COMMENT '是否删除',
`cu_id` int(11) DEFAULT NULL COMMENT '客户id',
`is_update` tinyint(4) DEFAULT '0' COMMENT '是否为更新',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 COMMENT='状态表';
-- 插入数据
INSERT INTO `zj_cus_column` VALUES ('11', '2', '0', '123', '0', '0');
INSERT INTO `zj_field_status` VALUES ('16', '测试数据11', '0', '1', null, '内容', '2019-05-17 15:36:58', '0', '2', null);
-- 新增字段触发器
CREATE TRIGGER add_field AFTER INSERT ON `zj_field_status` FOR EACH ROW
BEGIN
INSERT INTO `zj_cus_column` (`cu_id`, `table_name`, `col_name`, `field_id`) VALUES (new.cu_id, new.table_name, new.name,new.id);
END
-- 修改字段触发器
CREATE TRIGGER update_field AFTER update ON `zj_field_status` FOR EACH ROW
BEGIN
UPDATE `zj_cus_column` SET `cu_id`=new.cu_id, `table_name`= new.table_name, `col_name`= new.name WHERE (`field_id`=new.id);
END
-- 删除字段触发器
CREATE TRIGGER delete_field BEFORE delete ON `zj_field_status` FOR EACH ROW
BEGIN
DELETE FROM `zj_cus_column` WHERE (`field_id`= old.id);
END
-- 查询触发器
show triggers;
-- 删除触发器
drop trigger addfield;
new 获取插入后的值
old 获取将要执行语句的值