主题：[推荐]第六部分课堂讲稿——SQL语言之三

8 游标
游标是一种可以让用户逐行定位记录来操作表数据的特殊变量。

8．1 概念
强调它与基本表的关系，它与查询、视图的联系和区别。
强调它与SQL操纵记录方式的区别。
说明任何编程语言中对数据库的访问都是通过游标进行的，相关的语句也有很多，如VFP的skip、VB的moveNext()和Java的next()等。

8．2 定义和使用
利用游标显示男女生的汉字性别：
declare stuCh cursor
static
for select name,sex from stu

declare @num nvarchar(8)
declare @sex int

open stuCh
fetch first from stuCh into @num,@sex
while (@@fetch_status=0)
begin
    if @sex=1
        print @num+'——男'
    else
        print @num+'——女'
    fetch next from stuCh into @num,@sex
end
close stuCh
deallocate stuCh

输出每个学生平均成绩的优良中差：
declare stugrade cursor
static
for select number,avg(grade) from grade group by number

declare @num char(6)
declare @gra int

open stugrade
fetch first from stugrade into @num,@gra
while (@@fetch_status=0)
begin
    if @gra>85
        print @num+'优秀，平均成绩为'+convert(char(3),@gra)

    else
        print @num+'一般，平均成绩为'+convert(char(3),@gra)
    fetch next from stugrade into @num,@gra
end
close stugrade
deallocate stugrade

8．3 可更新的游标
将第一条记录的姓名加上一个“*”号：
DECLARE abc CURSOR FOR
SELECT name
FROM stu

OPEN abc
GO

FETCH NEXT FROM abc
GO

UPDATE stu SET name = name+'*' WHERE CURRENT OF abc
GO

CLOSE abc
DEALLOCATE abc
GO

10 触发器
使用触发器，可以让数据库产生主动和智能化的效果，它广泛的应用于各种大型数据库管理系统。
为说明必要性，举两个例子：
一是防止无意删除全部记录；
二是对下面的两个表实现一种特殊的约束，即两表的生日和年龄必须对应：
create table stu1
(
number char(4) not null,
birthday datetime
)

create table stu2
(
number char(4) not null,
age int
)

使用传统方法，解决这些问题就需要用户在客户端编程才能实现。通过触发器可以将这些逻辑功能内置在数据库中。不同于存储过程，这些触发器所关联的代码会在某种操作发生时自动被触发执行。

10．1 触发器的定义和使用
10．1．1 相关语句
create trigger ins
on stu
for insert
--delete update
as
print '添加记录一条'

可通过此语句观察：
insert into stu(number) values('000200')

对于更新操作的触发器，还可以判定操作的字段列：
create trigger ins1
on stu
for update
as
if update(name)
    print '添加姓名一个'

可通过下面的语句观察：
update stu set name='Tom' where number='000200'
update stu set height=2 where number='000200'

10．1．2 图形界面操作
在企业管理器中只有打开表设计器方可编辑触发器，右击表——“所有任务”——“管理触发器”。
10．2 INSERTED表和DELETED表
每次插入记录，都会将新插入的记录存入INSERTED表；
每次删除记录，都会将被删除的记录存入DELETED表；
每次更新记录，都会将旧记录存入DELETED表，再把新记录存入INSERTED表。

例1：查看被删除的记录：
create trigger delTri
on stu
for delete
as
select * from deleted

可通过此语句观察：
delete from stu where number='000200'

例2：查看被插入的记录：
create trigger insTri
on stu
for insert
as
select * from inserted

可通过此语句观察：
insert into stu(number) values('000300')

例3：查看更新前和更新后的记录：
create trigger updTri
on stu
for update
as
select * from deleted
select * from inserted

可通过此语句观察：
update stu set height=1 where number='000300'

10．3 综合应用
1）将删除记录自动备份
select * into stubak from stu where 1=2

create trigger backupDelRecs
on stu
for delete
as
select * into stubak from deleted

可通过此语句观察：
delete from stu where number='000006'

2）防止删除全部记录，当用户删除全部记录时，操作无效
create trigger antiDelAllRec
on stu
for delete
as
declare @total int
select @total=count(*) from stu
if @total=0
    insert into stu select * from deleted

可通过此语句观察：
delete from stu

3）对上述两个学生信息表实现一种特殊的约束，即两表的生日和年龄必须对应：
插入触发器：
create trigger triIns
on stu1
for insert
as
declare @num char(4)
declare @birth datetime
select @num=number from inserted
select @birth=birthday from inserted
insert into stu2 values(@num,year(getdate())-year(@birth))

更新触发器：
create trigger triUpd
on stu1
for update
as
if update(birthday)
    declare @num char(4)
    declare @birth datetime
    select @num=number from deleted
    select @birth=birthday from inserted
    update stu2 set age=year(getdate())-year(@birth) where number=@num

删除触发器：
create trigger triDel
on stu1
for delete
as
declare @num char(4)
select @num=number from deleted
delete from stu2 where number=@num