mysql触发器使用

触发器使用记录

--增量表

CREATE TABLE `biz_video_tri` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `videoId` int(11) NOT NULL COMMENT '所属视频',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `remark` varchar(255) DEFAULT NULL COMMENT '摘要',
  `employeeName` varchar(255) DEFAULT NULL COMMENT '发布者用户名',
  `schoolNo` varchar(100) DEFAULT NULL COMMENT '学校id',
  `gradeDescription` varchar(64) DEFAULT NULL COMMENT '年级',
  `categoryDescription` varchar(64) DEFAULT NULL COMMENT '学科',
  `url` varchar(255) DEFAULT NULL COMMENT 'url',
  `operType` varchar(10) DEFAULT NULL COMMENT 'add新增 del删除 upd更新',
  `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='视频资源增量表'

-- 新增触发器

drop trigger if exists education.biz_video_insert;
delimiter || （设置结束的分隔符）
create trigger education.biz_video_insert
after insert on education.biz_video
for each row
begin
declare gradeDescription varchar(255) ;    （声明变量）
declare categoryDescription varchar(255) ;
declare fileNameUri varchar(255) ;
set gradeDescription=(select description from biz_grade where id=new.gradeId);    （设置值）
set categoryDescription=(select description from biz_category where id=new.categoryId);

if (locate('.',new.fileName)>0)
  then
      set fileNameUri = (select concat('/upload/video/filename/',new.id,'/',new.id,'.',
      lower((select substring_index(new.fileName, '.', -1)))));
  Else
      set fileNameUri = (select concat('/upload/video/filename/',id,'/',new.fileName));
  end if;

insert into biz_video_tri(videoId,title,remark,schoolNo,employeeName,gradeDescription,categoryDescription,url,operType)
 values (new.id,new.title,new.remark,new.schoolNo,new.employeeNo,gradeDescription,categoryDescription,fileNameUri,'add');
 
end||
delimiter ;

-- 删除触发器

drop trigger if exists biz_viedo_before;
delimiter ||
create trigger biz_viedo_before 
before delete on biz_video
for each row
BEGIN
declare gradeDescription varchar(255)  ;
declare categoryDescription varchar(255)  ;
declare fileNameUri varchar(255)  ;
set gradeDescription=(select description from biz_grade where id=old.gradeId);
set categoryDescription=(select description from biz_category where id=old.categoryId);

if (locate('.',old.fileName)>0)
  then
      set fileNameUri = (select concat('/upload/video/filename/',old.id,'/',old.id,'.',
      lower((select substring_index(old.fileName, '.', -1)))));
  Else
      set fileNameUri = (select concat('/upload/video/filename/',id,'/',old.fileName));
  end if;

insert into biz_video_tri(videoId,title,remark,schoolNo,employeeName,gradeDescription,categoryDescription,url,operType)
 values (old.id,old.title,old.remark,old.schoolNo,old.employeeNo,gradeDescription,categoryDescription,fileNameUri,'del');
 
END||
delimiter ;

-- 更新触发器

drop trigger if exists biz_video_update;
delimiter ||
create trigger biz_video_update
after update on biz_video
for each row
begin
declare gradeDescription varchar(255)  ;
declare categoryDescription varchar(255)  ;
declare fileNameUri varchar(255)  ;
set gradeDescription=(select description from biz_grade where id=new.gradeId);
set categoryDescription=(select description from biz_category where id=new.categoryId);

if (locate('.',new.fileName)>0)
  then
      set fileNameUri = (select concat('/upload/video/filename/',new.id,'/',new.id,'.',
      lower((select substring_index(new.fileName, '.', -1)))));
  Else
      set fileNameUri = (select concat('/upload/video/filename/',id,'/',new.fileName));
  end if;
  
insert into biz_video_tri(videoId,title,remark,schoolNo,employeeName,gradeDescription,categoryDescription,url,operType)
 values (new.id,new.title,new.remark,new.schoolNo,new.employeeNo,gradeDescription,categoryDescription,fileNameUri,'upd');
  
end||
delimiter ;

####创建只有查看该视图权限的账号
CREATE USER 'aaa'@'%' IDENTIFIED BY 'aaa';
GRANT select,show view ON `aa` TO 'aaa'@'%';
GRANT select,update ON`biz_video_tri` TO 'aaa'@'%';
FLUSH PRIVILEGES;