【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