--触发器分类:(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'