SQL Server—触发器

1、触发器的定义：可以理解为因为发生了某一件事情，而自动的发生了下一件事情。

2、触发器的分类

（1）“instead of”触发器：在执行操作之前被执行。–事前触发器

（2）“after”触发器：在执行操作之后被执行。 –事后触发器

/*
instead：替代/而不是…/代替/反而
instead of：而不是
*/

举例：假设有部门表和员工表，在添加员工的时候，该员工的部门编号如果在部门表中找不到，则自动添加部门信息，部门名称为”新部门”。

create trigger tri_InsertPeople on People after insert
as
    if not exists(select * from Department where DepartmentId = (select DepartmentId from inserted))
    begin
        insert into Department(DepartmentId,DepartmentName)
        values((select DepartmentId from inserted),'新部门')
    end
go

/*
trigger：引发 / 开动 / 触发
tri_InsertPeople是触发器的名字
*/

举例：创建一个触发器，删除一个部门的时候判断该部门下是否有员工，有则不删除，没有则删除。

create trigger tri_DeleteDept on Department instead of delete
as
    if not exists(select * from People where DepartmentId = (select DepartmentId from deleted))
    delete from Department where DepartmentId = (select DepartmentId from deleted)
go

举例：触发器实现，删除一个部门的时候将部门下所有员工全部删除。

create trigger tri_DeleteDept on Department after delete
as
    delete from People where DepartmentId = (select DepartmentId from deleted)
go

举例：修改一个部门编号之后，将该部门下所有员工的部门编号同步进行修改

create trigger tri_UpdateDept on Department after update
as
    update People set DepartmentId = (select DepartmentId from inserted)
    where DepartmentId = (select DepartmentId from deleted)
go

注意：在项目中不要过多的使用触发器，触发器的使用需要谨慎再谨慎！！

如果有多个表之间存在触发器，删除一个表内的数据，有可能会删除其他表的数据（但这个表的数据是你并不想删除的）。