数据库 实验五 完整性控制

1. 创建一个触发器，“数据库”课程的选课人数不能超过5人。

CREATE TRIGGER S_1 ON SC
AFTER INSERT AS
	DECLARE @COUNT INT,@Sno CHAR(9),@Cno CHAR(4),@Grade SMALLINT;
	SELECT @COUNT=COUNT(*) FROM Course,SC WHERE Course.Cno=SC.Cno AND Course.Cname='数据库'
	SELECT @Sno=Sno,@Cno=Cno,@Grade=Grade FROM INSERTED;
IF(@COUNT>5)
  	BEGIN
    		DELETE FROM SC WHERE @Sno=Sno AND @Cno=Cno
  	END

    查询现有数据

	查询现有数据
SELECT * FROM Course,SC 
WHERE Course.Cno=SC.Cno 
AND Course.Cname='数据库';

删除sc表中所有数据

DELETE FROM SC;

重新插入sc表数据

INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','8',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215123 ','4',85);

再进行进行查询

	SELECT * FROM Course,SC 
WHERE Course.Cno=SC.Cno 
AND Course.Cname='数据库';

删除触发器S_1

2. 创建一个触发器，规定计科系每位同学选课门数不能超过3门。

CREATE TRIGGER S_2 ON SC
AFTER INSERT AS
	DECLARE @COUNT INT,@Sno CHAR(9),@Cno CHAR(4),@Grade SMALLINT;
	SELECT @Sno=Sno,@Cno=Cno,@Grade=Grade FROM INSERTED;
	SELECT @COUNT=COUNT(*) FROM Student WHERE Sdept='计科系'
IF(@COUNT>3)
	BEGIN
		DELETE FROM SC WHERE @Sno=Sno AND @Cno=Cno
	END;

查询现有数据

SELECT * FROM Course,SC 
WHERE Course.Cno=SC.Cno 
ORDER BY SC.Sno;

删除SC表中的所有数据

DELETE FROM SC;

重新插入数据

INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','8',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215123 ','4',85);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215123 ','5',88);

再进行查询

SELECT * FROM Student,Course,SC 
WHERE Course.Cno=SC.Cno 
ORDER BY SC.Sno;

3. 创建一个触发器，当修改成绩表中成绩时，如果变化幅度超过10%，将操作记录在下表SC_U（Sno,Cno,Oldgrade,Newgrade）中。

创建表SC_U

CREATE TABLE SC_U(
  Sno CHAR(9), 
  Cno CHAR(4),  
  OldGrade SMALLINT,
  NewGrade SMALLINT
  PRIMARY KEY (Sno,Cno),
  FOREIGN KEY (Sno) REFERENCES T.Student(Sno),
  FOREIGN KEY (Cno) REFERENCES T.Course(Cno)
);

创建触发器S_3

         

CREATE TRIGGER S_3 ON SC
AFTER UPDATE AS
	DECLARE @COUNT INT,@Sno CHAR(9),@Cno CHAR(4),@OldGrade SMALLINT,@NewGrade SMALLINT;
IF UPDATE(Grade)
BEGIN
	SELECT @Sno=Sno,@Cno=Cno,@NewGrade=Grade FROM INSERTED;
	SELECT @Sno=Sno,@Cno=Cno,@OldGrade=Grade FROM DELETED;
IF(@NewGrade > @OldGrade+@OldGrade*0.1)
	BEGIN
		INSERT INTO SC_U VALUES(@Sno,@Cno,@OldGrade,@NewGrade)
	END
END

查询之前的成绩        

SElECT * FROM SC
WHERE Cno = '3'
AND Sno='201215122'

更新成绩

UPDATE SC SET Grade = '100' 
WHERE Cno = '3' 
AND Sno='201215122';

查询更新后成绩

SElECT * FROM SC  WHERE Cno = '3' 
AND Sno='201215122';

查询SC_U看是否有记录

SELECT * FROM SC_U;