触发器使用记录
--增量表
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;