触发器和存储过程

设有 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

 

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

触发器和存储过程

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭