数据库——存储，触发器

数据库——存储，触发器

/------存储1 根据用户输入该学生编号，检查是否存在，若存在则显示出/

Create procedure p1
@sno char(10) ,@sname char(10),@ssex char(2),@borndate datetime,@classname varchar(50)
As
Begin
If exists(select * from student2 where sno=@sno)
Select * from student2 where sno=@sno
Else
Begin
insert into student2 (sno,sname,ssex,borndate,classname)
values(@sno,@sname,@ssex,@borndate,@classname)
End
Select * from student2 where sno=@sno
end
exec p1 ‘100109’,‘鹬蚌’,‘男’,‘2000-6-8’,‘计算机班’

/-----存储2 如果存在编号为“100112”的学生则将其信息删除，如果不存在，则显示“没有这个学生”/

Create procedure f6
as
begin
if exists(select * from student2 where sno=‘100112’)
begin
delete from student2 where sno=‘100112’
delete from student2 where sno=‘100112’
end
else
print’没有这个学生’
end
exec f6

/-----存储3使用形参名进行传值/

Create procedure g6
@sno char(10),@score char(10),@rank char(10)
As
Begin
If exists(select * from student3 where sno=@sno)
Select * from student3 where sno=@sno
Else
Begin
insert into student3 (sno,score,rank)
values(@sno,@score,@rank)
End
Select * from student3 where sno=@sno
end
Exec g6 @sno=‘100104’,@score=‘86’,@rank=‘4’

/–触发器1删除这个学生的信息，同时删除他的记录/

Create trigger a2
on student2
for delete
as
begin
if exists(select sno from deleted)
begin
delete from student2 where sno=(select sno from deleted)
end
else
print’没有这个学生’
end
Delete from student2 where sno=‘100112’

/—触发器2 当更新这个学生编号的同时，更新这个学生的其他信息/

create trigger q6
On student2
for update
As
begin
if exists(select sno from deleted)
begin
update student2
set sno=(select sno from inserted)
where sno=(select sno from deleted)
end
else
print’找不到这个学生的记录’
end
update student2 set sno='100105’where sno=‘100101’