-- 触发器(trigger)
/*触发器是与表有关的数据库对象,指在insert/update/delete之前(BEFORE)或之后(AFTER),触
发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性
, 日志记录 , 数据校验等操作 。
*/
/*
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还
只支持行级触发,不支持语句级触发。
*/
-- 创建按触发器
create trigger trigger_insert
after insert on employee for each row
begin
declare c int;
declare a date;
set c = (select id from employee where id = NEW.id);
insert into log values(NEW.id,new.name,now());
end;
desc employee;
-- 创建日志表
create table Log(
id int comment '日志id',
name varchar(30) comment '姓名',
date date comment '发生时间'
);
alter table log change date datetime datetime comment'发生时间';
-- 查看触发器
show triggers ;
-- 测试触发器
insert into employee values(11,'李白',559,'男','宣传部',2450,2);
insert into employee values(13,'岳飞',35,null,null,null,null);
insert into employee values(14,'高俅',48,null,null,null,null);
select * from log;
select * from employee;
li练习
-- 创建触发器,在employee表中删除员工信息的同时将salary表中该员工的信息删除,以确保数据完整性。
-- 创建完后尝试删除employee表中的一行数据,然后查看salary表中的变化情况
create trigger trigger_delete
after delete on employee for each row
begin
delete from salary where salary.employeeID = OLD.employeeID;
end;
-- 测试删除一条数据
delete from employee where employeeID = '000001';
select * from employee;
select * from salary;
-- 当修改employee表时,若将employee表中的员工工作时间增加一年,则将收入增加500,增加两年则收入增加1000,以此类推
create trigger trigger_update
after update on employee for each row
begin
declare n int;
set n = (select new.workYear)-(select old.workYear);
update salary set income =500*n+income where employeeID = new.employeeID;
end;
-- 测试更新薪资
update employee set workYear = workYear-2 where employeeID = '000001';
select * from salary;
select * from employee;
desc employee;
-- 恢复数据
insert into employee values('000001','王浩','大专','1966-01-23',1,8,'中山路32号','83355668','2');
insert into salary values('000001',2100.8,123.09);