SQL触发器

触发器是与表有关的数据库对象。

在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发
器中定义的SQL语句集合。

触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

CREATE TABLE employee(
	employee_ID int not null,
	employee_name varchar(20) not null,
	street varchar(20) not null,
	city varchar(20) not null,
	PRIMARY KEY(employee_ID)
);

create table works(
	employee_ID int not null,
	company_name varchar(30),
	salary numeric(8,2) check (salary>3000),  
	primary key(employee_ID),
	foreign key(employee_ID) references employee(employee_ID) on delete cascade,
	foreign key(company_name) references company(company_name) on delete set null									
);

create table sav1(
	company_name varchar(30) not null,
	avg_salary numeric(8,2) check (avg_salary>3000),
	primary key(company_name)
);

create table myevent(
	employee_ID int not null,
	employee_name varchar(20) not null,
	salary numeric(8,2)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

1.在表SAV1上定义一个update触发器trig_update。当修改某个员工的工资后，自动重新计算每个公司的平均工资，并更新到SAV1表中。

create trigger trig_update 
after update on works for each row 
begin
	update sav1
	set sav1.avg_salary=(
		select avg(works.salary) 
		from works 
		where works.company_name=new.company_name)
	where sav1.company_name=new.company_name;
end;
1
2
3
4
5
6
7
8
9
10

2.在表SAV1上定义一个insert触发器trig_insert。当插入一个员工的工资后，自动重新计算每个公司的平均工资，并更新到SAV1表中。

create trigger trig_insert 
after insert on works for each row 
begin
	update sav1
	set sav1.avg_salary=(
		select avg(works.salary) 
		from works 
		where works.company_name=new.company_name)
	where sav1.company_name=new.company_name;
end;
1
2
3
4
5
6
7
8
9
10

3.在表SAV1上定义一个delete触发器trig_delete。当删除一个员工的工资后，自动重新计算每个公司的平均工资，并更新到SAV1表中。

# MYSQL5.0触发程序不会被级联的外键动作激活,Oracle,DB2,SQL Server都支持
create trigger trig_delete 
after delete on works for each row
begin
	update sav1
	set sav1.avg_salary=(
		select avg(works.salary) 
		from works 
		where works.company_name=old.company_name)
	where sav1.company_name=old.company_name;
end;
1
2
3
4
5
6
7
8
9
10
11

直接在employee表中删除‘MrDeng’的员工的信息，因为有外键约束，works表中相关信息也会被删除。

delete from employee where employee_name='MrDeng';  
1

mysql 5.* 版本在级联删除时不会执行delete触发器，比如 A、B 两个表，A 是主键表，B 是外键表，级联删除。那么 A 表中的记录被删除时，B 表中对应的记录也将被自动删除，如果 B 表中有针对 DELETE 的触发器，这个触发器不会被执行。
Oracle,DB2,SQL Server都支持级联删除。
解决办法：在A表中创建一个触发器间接触发B的触发器。

create trigger temp_trig_delete 
before delete on employee for each row
begin
	delete from works where works.employee_ID=old.employee_ID;
end;
1
2
3
4
5

4.创建一个update_salary的触发器，每次向works表更新数据之后都会向名称为myevent的数据表中插入一条记录，其中myevent表包含三个属性：employee_ID，employee_name和 salary。

create trigger update_salary
after insert on works for each row
begin
	insert into myevent(employee_ID,employee_name,salary) values (new.employee_ID,(select employee_name from employee where employee.employee_ID=new.employee_ID),new.salary);
end;

1
2
3
4
5
6

5.删除触发器trig_update。

drop trigger trig_update;
1

6.查看触发器的信息。

show triggers;

select * from information_schema.triggers;
1
2
3