关闭

触发器和存储过程

442人阅读 评论(0) 收藏 举报

设有 Student(CNO,CNAME,SEX,AGE,SCORE)
表示学生的(学号、姓名、性别、年龄、学分)
SC(CNO,COURSE,Score)表示学生的(学号,课程,学分)实现下面触发器:
1。增加触发器:增加学分记录时,增加SCORE
2。删除触发器:删除学分记录时,减少SCORE
3。更新触发器:更新学分记录时,更改SCORE

CREATE TRIGGER AddScore ON [SC]
FOR INSERT
AS
begin
  declare @CNO1 int
  declaer @COURSE1 char
  declare @Score1 float
  select @CNO1=CNO,@COURSE1=COURSE,@Score1=Score from inserted
  update Student set SCORE=SCORE+@Score1 where
  CNO=@CNO1
end
go

insert into SC values(1,"hh",22)

CREATE TRIGGER DelScore ON [SC]
FOR DELETE
AS
begin
  declare @CNO1 int
  declaer @COURSE1 char
  declare @Score1 float
  select @CNO1=CNO,@COURSE1=COURSE,@Score1=Score from inserted
  update Student set SCORE=SCORE-@Score1 where
  CNO=@CNO1
end
go
s
delete into SC values(1,"hh",22)

CREATE TRIGGER DelScore ON [SC]
FOR DELETE
AS
begin
  declare @CNO1 int
  declaer @COURSE1 char
  declare @Score1 float
  select @CNO1=CNO,@COURSE1=COURSE,@Score1=Score from inserted
  update Student set SCORE=SCORE-@Score1 where
  CNO=@CNO1
select @CNO1=CNO,@COURSE1=COURSE,@Score1=Score from inserted
  update Student set SCORE=SCORE+@Score1 where
  CNO=@CNO1
end
go

update into SC values(1,"hh",22)


1)存储过程的练习
设有Student(CNO,CNAME,SEX,AGE)
设计一个存储过程 实现如下的查询
SEX=xx and AGE<=YY

CREATE PROCEDURE r
   @SEX1 char,
   @AGE1 int
AS
select * from Student
where SEX=@SEX1 and AGE<=@AGE1

GO


exec r '女',50

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:4605次
    • 积分:108
    • 等级:
    • 排名:千里之外
    • 原创:4篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章存档