SQL触发器

--触发器分类：（1） “Instead of”触发器（2）“After”触发器

--（1） “Instead of”触发器：在执行操作之前被执行

--（2）“After”触发器：在执行操作之后被执行

--部门

create table Department

(

    DepartmentId varchar(10) primary key , --主键，自动增长

    DepartmentName nvarchar(50), --部门名称

)

--人员信息

create table People

(

    PeopleId int primary key identity(1,1), --主键,自动增长

    DepartmentId varchar(10), --部门编号,外键,与部门表关联

    PeopleName nvarchar(20), --人员姓名

    PeopleSex nvarchar(2), --人员性别

    PeoplePhone nvarchar(20), --电话，联系方式

)

insert into Department(DepartmentId,DepartmentName)

values('001','总经办')

insert into Department(DepartmentId,DepartmentName)

values('002','市场部')

insert into Department(DepartmentId,DepartmentName)

values('003','人事部')

insert into Department(DepartmentId,DepartmentName)

values('004','财务部')

insert into Department(DepartmentId,DepartmentName)

values('005','软件部')

insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)

values('001','刘备','男','13558785478')

insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)

values('001','关羽','男','13558788785')

insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)

values('002','张飞','男','13698547125')

select *from Department

select*from People

--(1)假设有部门表和员工表，在添加员工的时候，该员工的部门编号如果在部门表中找不到，则自动添加部门信息，部门名称为‘新部门’

create trigger tri_InsertPeople on People after insert

as

  if not exists(select *from Department where DepartmentId= (select DepartmentId from inserted))--添加的信息会存在inserted里面

    begin

      insert into Department(DepartmentId,DepartmentName)

      values((select DepartmentId from inserted),'新部门')

    end

go

--测试触发器

insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)

values('003','赵云','男','13558788785')

insert into People(DepartmentId,PeopleName,PeopleSex,PeoplePhone)

values('006','马超','男','13698547125')

--(2)触发器实现，删除一个部门的时候，其部门下的员工全部删除

create trigger tri_DeleteDept on Department after delete

as

  delete from People where DepartmentId=(select DepartmentId from deleted) --删除的信息会存在deleted里面

go

--测试触发器

select *from Department

select*from People

delete from Department where DepartmentId='006'

--(3)创建一个触发器，删除一个部门的时候判断该部门门下是否还要员工，有则不删除，无则删除

drop trigger tri_DeleteDept

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

--测试触发器

select *from Department

select*from People

delete from Department where DepartmentId='001'