【sql server 2000学习笔记3】transaction,触发器,游标

use grademanager
go
select * 
from student
--事务transaction
begin transaction
insert into student values('00','严明超','男',21, 08085511);
--insert into student values(0,'0','严明超','男',21, 08085511);
commit transaction
go
select *
from student;
go
delete 
from  student
where sname = '严明超'
go
begin transaction 
insert into student values('00','严明超','男',21, 08085511);
rollback transaction
go
select *
from student
 execute studentinfo
--truncate table
select *
from grade
select *
from course
declare @i smallint
set @i = 1;
while @i<=7
	begin
		insert into
		grade 
		values('2000101',convert(char(1),@i), 60);
		set @i = @i+1;
	end
go
create table sca (
	sno char(7) primary key,
	sname varchar(20) not null,
	ssex char(2) not null ,
	sage smallint not null,
	clno char(5) not null
)
insert 
into sca 
 select *
 from student
 where not exists
 	(select *
	from course
	where not exists
		(select *
		from grade
		where sno = student.sno and cno = course.cno))
select *
from sca
truncate table sca
--触发器
select *
from student
create trigger tri_del on student
instead of delete
as
declare  @sex char(2)
declare @id char(7)
select @sex = ssex from deleted
select @id  = sno from deleted
if(@sex = '女')
	print '为女生不允许删'
else
	delete from student where sno = @id
execute studentinfo
insert into student values('00','严明超','男',21, 08085511);
insert into student values('01','mwt','女',21, 08085511);
delete
from student 
where sno = '00'
delete 
from student 
where sno = '01';
--触发器加密
alter trigger tri_del on student
with encryption
instead of delete
as
declare  @sex char(2)
declare @id char(7)
select @sex = ssex from deleted
select @id  = sno from deleted
if(@sex = '女')
	print '为女生不允许删'
else
	delete from student where sno = @id
--游标
/*
1.声明
2. 打开
3.读取
4.关闭
5.删除
*/
declare cur_male scroll cursor
	for select * from student where ssex = '男'
open cur_male 
fetch first from cur_male
close cur_male
deallocate cur_male