第6章 触发(上海电力学院).pptVIP

第六章 触发器 学习重点: ?掌握触发器的创建 ?掌握触发器的删除、修改、执行方法 ?掌握管理和维护触发器的方法 一、内容回顾 ?概述 三、创建触发器语法： CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {????{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }????????[ WITH APPEND ]????????[ NOT FOR REPLICATION ]????????AS????????[ { IF UPDATE ( column )????????????[ { AND | OR } UPDATE ( column ) ]????????????????[ ...n ]????????| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )????????????????{ comparison_operator } column_bitmask [ ...n ]????????} ] ????????sql_statement [ ...n ] ????} } 方法二:使用触发器,前提是SC表没有定义和student表相关的外键 create trigger stu_del on student for delete as Begin Begin transaction declare @stu_sno char(8) select @stu_sno=deleted.sno from deleted delete from sc where sc.sno=@stu_sno Commit tran End 实例3： insert into sc values(‘2006002’,’c001’,80) ，如何修改他的已修学分？ Create triger update_credit On student After insert As Begin declare @c_no char(4), @s_no char(8),@cdit int select @c_no=cno,@s_no=sno from inserted select @cdit=credit from course where cno=@c_no update student set credit =credit+@cdit where sno=@s_no end Employee表与jobs表的关系图如下： else begin update student set sumgrade=(select sum(grade) from sc where sc.sno=student.sno) where sno in (select sno from deleted) end commit transaction 另一种方法: create trigger sc_change on sc for update,insert,delete as begin transaction if exists(select * from deleted) begin update student set sumgrade=(select sum(grade) from sc where sc.sno=student.sno) where sno in (select sno from deleted) end else begin update student set sumgrade=(select sum(grade) from sc where sc.sno=student.sno) where sno in (select sno from inserted) end commit transaction 实例7、使用延迟名称解析 USE pubs CREATE TRIGGER trig1 on authors FOR INSERT, UPDATE, DELETE AS SELECT a.au_lname, a.au_fname