SQL server数据库基础——定义和删除触发器
触发器做完整性检查,可以自动执行。基于某一个行为自动触发逻辑表:
① inserted :用于存放插入之后的记录(新记录);
② deleted :用于存放删除之后的记录(原记录)。
1. DML (数据操纵语言)触发器
after (后)触发器:执行 Insert、Update、Delete 之后触发。
Instead of (前)触发器:先执行触发器本身定义的操作,符合完整性则执行 Insert、Update、Delete 操作。
-- 创建insert触发器,如果向教师信息表TeachInfo中插入记录时,检查该记录的院系编号在院系信息表DepInfo中是否存在,如果不存在,则不允许插入
create trigger trig_insertTeachinfo
on dbo.TeachInfo
after insert
as
begin transaction -- transaction 事务
if exists (
select * from inserted a -- inserted 是逻辑表,用于存放插入之后的记录
where a.DepInfoID not in (
select DepInfoID from dbo.DepInfo
)
)
begin
raiserror('数据一致性验证!',16,1) -- '错误的描述',错误的严重级别代码,错误的标识
rollback transaction -- 回滚事务
end
else
commit transaction -- 提交事务
end
-- 创建 update 触发器,当班级信息表ClassInfo中的主键ClassInfoID进行修改时,必须对学生表中相应的ClassInfoID也进行修改
-- update 操作分为两个步骤:1. 删除原来的数据 2. 添加新的数据
create trigger trig_updateClass
on dbo.ClassInfo
after update
as
if update(ClassInfoID) -- 限定只有在对主键修改时在进行触发器操作
begin
update dbo.StudentInfo
set ClassInfoID = (
select ClassInfoID from inserted -- inserted 修改之后的记录
)
where ClassInfoID = (
select ClassInfoID from deleted -- deleted 原先删除的记录
)
end
-- 创建 delete 触发器,当删除学生信息表StudentInfo中已退学的学生资料时,自动删除学生选课表StudentCourse中此学生相应的所有信息
create trigger trig_deleteStudent
on dbo.StudentInfo
after delete
as
begin
declare @stdInfoID int
select @stdInfoID = deleted.StdInfoID from deleted
delete dbo.StudentCourse
where StdInfoID = @stdInfoID
end
-- 创建instead of触发器,限制不允许删除院系信息表DepInfo中已存在的院系记录
create trigger trig_deleteDep
on dbo.DepInfo
instead of delete
as
begin
declare @delCount int
select @delCount = count(*) from deleted
if @delCount > 0
begin
rasierror('不能删除院系表中的任何记录!',10,1)
rollback transaction
end
end
-- 修改触发器
alter trigger trig_deleteDep
on dbo.DepInfo
instead of delete
as
begin
...............
end
-- 修改和删除触发器
drop trigger trig_deleteDep
2. DDL (数据定义语言)触发器
DDL 触发器在 create、alter、drop 及其他 DDL 语句上操作;
DDL 触发器只能作为后(after)触发器使用,不能作为前触发器。
-- 定义 DDL 触发器来防止数据库StuSelDB 中的任一表被修改或删除
create trigger trig_DBsafe
on databaase -- 触发器建立在数据库上,这里不能直接写数据库名字
for drop_table,alter_table
as
print '你不能删除或修改任何对象!'
rollback
-- 删除 DDL 触发器
drop trigger trig_DBsafe
on database
3. 一个触发器定义两个操作
-- 创建触发器,当在学生信息表中插入或删除记录时,班级信息表ClassInfo中的班级人数进行实时更新。
create trigger trig_StuInfoClassNum
on dbo.StudentInfo
after insert,delete
as
if(exists (select * from inserted)) -- 如果插入则执行
begin
update dbo.ClassInfo
set ClassInfoSum += 1
where ClassInfoID in (
select ClassInfoID from inserted
)
end
if(exists (select * from deleted)) -- 如果删除则执行
begin
update dbo.ClassInfo
set ClassInfoSum += 1
where ClassInfoID in (
select ClassInfoID from deleted
)
end