触发器

什么是触发器

触发器是个特殊的存储过程，不是由用户触发，是系统根据事件来触发。对表操作比如
添加，删除，修改。 也就是在进行DML操作的时候可以触发我们自己写的逻辑

触发器的作用：

数据同步（数据同步更新，同步修改）， 做一些数据自定义约束.....,可以根据具体业务来实现

触发时间：

操作之前和操作之后，比如添加之前执行，添加之后执行，更新之前，更新之后......

操作之前： instead of [事件]
操作之后： after [事件] / for [事件]

触发器的语法：

create trigger 触发器名称
on 表 [时间] [事件]
as
begin
........触发后具体要实现的功能...........
end

删除触发器：drop trigger 触发器名称

deleted：删除数据时，临时存放被删除的数据(一行记录)

inserted：插入数据时，临时存放即将插入的数据(一行记录)

基本使用：

用户表添加之后执行的触发器:

--exp:添加员工的时候，如果部门编号找不到，则自动添加部门信息，部门名称为"新部门"

create trigger tri_InsertPeople on People  --tri_InsertPeople:触发器名字
after insert								--Department：部门表
as											--DepartmentId：部门id
begin
if not exists(select * from Department where DepartmentId = (select DepartmentId from inserted))
	insert into Department(DepartmentId,DepartmentName)
	values((select DepartmentId from inserted),'新部门')
end
go

用户表删除之后执行的触发器:

--exp:触发器实现，删除一个部门的时候将部门下所有员工全部删除	
create trigger tri_DeleteDept on Department
after delete
as
begin
delete from People where People.DepartmentId = 
(select DepartmentId from deleted)
end
go

用户表删除之前执行的触发器:

--exp:创建一个触发器，删除一个部门的时候判断该部门下是否有员工，有则不删除，没有则删除
create trigger tri_DeleteDept on Department
Instead of delete
as
begin
  if not exists(select * from People where DepartmentId = (select DepartmentId from deleted))
  begin
	delete from Department where DepartmentId = (select DepartmentId from deleted)
  end
end
go

用户表修改之后执行的触发器:

修改其实就是先把数据删除，存在，deleted里，然后在从inserted里插入

--exp:修改一个部门编号之后，将该部门下所有员工的部门编号同步进行修改
create trigger tri_UpdateDept on Department
after update
as
	update People set DepartmentId = (select DepartmentId from inserted)
	where DepartmentId = (select DepartmentId from  deleted)
go