触发器

--在customer表上创建名为'custDel'的触发器 的触发器 create trigger custDel on customer for delete --指定触发事件，当执行delete delete语句时该触发器被触发 as print '客户记录被删除'
触发器语法
4
•
测试custDel触发器
5
安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
触发器类型
• 触发器的类型
– – – – INSERT UPDATE DELETE INSTEAD OF
6
安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
安博·思软培训层 山西·太原·南内环街鼎太风华 层
触发器
• 触发器是另一种类型的强制数据完整性的机制 触发器是另一种类型的强制数据完整性的机制。可 实现多个表之间数据的一致性和完整性。 实现多个表之间数据的一致性和完整性 • 触发器是一种特殊类型的存储过程 触发器是一种特殊类型的存储过程，它是通过事件 触发而被执行的。
create view emp_job as select e.empid,name,age,job,salary from employee e inner join jobs j on e.empid = j.empid
18
) --雇员任职历史表 create table jobs( id int identity(1,1) primary key , empid char(5), job varchar(30), salary money, foreign key(empid) references employee(empid) ) 安博· 安博·思软
南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
Instead of of触发器
• 将对emp_job视图的插入操作替换成对两个基表的 视图的插入操作替换成对两个基表的 插入操作
create trigger ins_emp_job on emp_job instead of insert as insert into employee select empid, name, age from inserted , insert into jobs select empid, job, salary from inserted ,
create trigger insertScore on student_course insert •for示例：在插入学生成绩时记录学分 在插入学生成绩时记录学分 as if (select score from inserted) >= 60 begin declare @stuid char(6), @couid char(3), @credit int --从inserted表中找出学生编号和课程编号 表中找出学生编号和课程编号,存放在变量中 select @stuid=stuid, @couid=couid from inserted --从课程表中根据课程编号找到该门课程的学分 从课程表中根据课程编号找到该门课程的学分 select @credit = couCredit from course where couid = @couid --在学生表中根据学生编号为学生增加学分 在学生表中根据学生编号为学生增加学分 update student set credit = credit + @credit where stuid = @stuid end 安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
12
• Update触发器有两种
– –
安博· 安博·思软
表级UPDATE UPDATE触发器
• 对表的任何更新都会启动 对表的任何更新都会启动表级UPDATE触发器
create trigger updateCouHour on course for update as ……
11

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
Update触发器 Update
• 在表上执行更新操作时， ，UPDATE触发器会执行下 列操作：
– – 将原始数据行移动到deleted deleted表中 将一个新行先插入到inserted inserted表中，然后再插入到触 发器所依赖的表中 表级UPDATE触发器 列级UPDATE触发器
南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
• 这两个表主要保存因用户操作而影响到的原数据值 和新数据值，通过这两个表可以访问正在被处理的 通过这两个表可以访问正在被处理的 行。
触发器类型 INSERT UPDATE DELETE inserted表 存储所插入的记录的副 本 存储更新了的记录的副 本 无 deleted表 无 存储更新前的原始记录的 副本 存储所删除的原始记录的 副本
南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
Inserted表和 表和Deleted表
8
安博· 安博·思软

• 创建一个触发器以实现自动更新专业统计信息的功 能 create trigger UpdateMajorStat
on student for insert, update, delete as begin delete from major_stat /*先删除 先删除major_stat表中的所有记录*/ /*按专业分组统计学生人数和总积分 按专业分组统计学生人数和总积分,将统计结果插入表中*/ insert into major_stat select majorName, count(*), sum(credit) , from student s inner join major m on s.majorid = m.majorid group by majorName end 安博· 安博·思软
2
安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
触发器
• 触发器的功能
– – – 强化约束 跟踪变化 级联运行
3
安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
• 一个简单的触发器：当删除 当删除customer表中的记录时， 显示一条提示信息
16
安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
Instead of of触发器
• INSTEAD OF触发器用于指定仅执行触发器中的操 触发器用于指定仅执行触发器中的操 作语句，而不执行触发的 而不执行触发的SQL语句，从而替代原始 的SQL语句的操作。 • INSTEAD OF触发器的一个主要用途是对不能更新 触发器的一个主要用途是对不能更新 的视图支持更新
create trigger noUpdateCouCredit on course for update as if update(couCredit) ……
14
安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
Delete触发器 Delete
• 当试图从表中删除数据行时 当试图从表中删除数据行时，DELETE触发器会执 行下列操作：
安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
触发器语法
• 具体的语法如下
CREATE TRIGGER [ schema_name . ] trigger_name ON { table | view } [ WITH ENCRYPTION ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ NOT FOR REPLICATION ] AS { sql_statement }
– – 从触发器表中删除行 将删除的行插入到deleted deleted表中
15
安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
Delete触发器 Delete
• Delete触发器一个重要作用就是备份数据 一个重要作用就是备份数据
create trigger delStuBackup on student for delete as insert into quit_stu select * from deleted
– – 向inserted表中插入一个新行的副本 表中插入一个新行的副本 检查inserted表中的新行是否有效 表中的新行是否有效，如果有效则插入 表中，否则阻止该插入操作 否则阻止该插入操作
10
安博· 安博·思软

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
Insert触发器 Insert

南内环街鼎太风华14层 山西·太原·南内环街鼎太风华 层
创建触发器
9
Insert触发器 Insert
• INSERT触发器可以确保添加到表中的数据是有效 触发器可以确保添加到表中的数据是有效 的 • 当试图向表中插入数据时 当试图向表中插入数据时，INSERT触发器执行下 列操作
Inserted表和 表和Deleted表
• Inserted表和Deleted表由 表由SQL Server自行创建和 管理 • 这两个表总是与被触发器作用的表有相同的表结构 ，它们是动态地驻留在内存中 它们是动态地驻留在内存中，当触发器工作完成 ，这两个表也被删除
7
安博· 安博·思软

17
安博· 安博·思软