--数据准备 --学生信息表 IF OBJECT_ID (N'Students', N'U') IS NOT NULL DROP TABLE Students; GO CREATE TABLE Students( ID int primary key not null, Name nvarchar(50), Age int, City nvarchar(50), MajorID int ) --专业信息表 IF OBJECT_ID (N'Majors', N'U') IS NOT NULL DROP TABLE Majors; GO CREATE TABLE Majors( ID int primary key not null, Name nvarchar(50) ) --课程表 IF OBJECT_ID (N'Courses', N'U') IS NOT NULL DROP TABLE Courses; GO CREATE TABLE Courses( ID int primary key not null, Name nvarchar(50) not null ) IF OBJECT_ID (N'SC', N'U') IS NOT NULL DROP TABLE SC; GO --选课表 CREATE TABLE SC( StudentID int not null, CourseID int not null, Score int ) /* 基础数据 */ --专业信息 DELETE FROM Majors INSERT INTO Majors(ID,Name) VALUES(10,'法律') INSERT INTO Majors(ID,Name) VALUES(11,'美学') --课程信息 DELETE FROM Courses INSERT INTO Courses(ID,Name) VALUES (10,'太极拳') INSERT INTO Courses(ID,Name) VALUES (11,'摄影入门') INSERT INTO Courses(ID,Name) VALUES (12,'生命科学导论') --学生信息 DELETE FROM Students INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10) INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(103,'李明',20,'BeiJing',11) INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(104,'王涛',18,'ShangHai',11) --选课信息 DELETE FROM SC INSERT INTO SC(StudentID,CourseID) VALUES(101,10) INSERT INTO SC(StudentID,CourseID) VALUES(101,11) INSERT INTO SC(StudentID,CourseID) VALUES(102,12) --触发器新增：只允许录取18岁以上学生 IF OBJECT_ID (N'TRIGER_Students_Insert', N'tr') IS NOT NULL DROP TRIGGER TRIGER_Students_Insert; GO CREATE TRIGGER TRIGER_Students_Insert ON Students FOR INSERT AS declare @age int select @age=COUNT(Students.ID) FROM Students INNER JOIN inserted ON Students.ID =inserted.ID PRINT @age if(@age<18) begin raiserror('学生年龄必须要大于18哦',16,8) rollback tran end --测试用例 INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(105,'李四',16,'BeiJing',11) SELECT * FROM Students --更新触发器：更新专业ID时,同时更新学生的专业信息 IF OBJECT_ID (N'TRIGER_Majors_Update', N'tr') IS NOT NULL DROP TRIGGER TRIGER_Majors_Update; GO CREATE TRIGGER TRIGER_Majors_Update ON Majors FOR UPDATE AS IF UPDATE(ID) UPDATE Students Set MajorID=inserted.ID FROM Students,deleted,inserted WHERE Students.MajorID = deleted.ID --测试用例 UPDATE Majors SET ID=12 WHERE ID=11 SELECT * FROM Students SELECT * FROM Majors --删除触发器：删除课程时,同时删除该课程的选课信息 IF OBJECT_ID (N'TRIGER_Courses_Delete', N'tr') IS NOT NULL DROP TRIGGER TRIGER_Courses_Delete; GO CREATE TRIGGER TRIGER_Courses_Delete ON Courses FOR DELETE AS DELETE SC FROM SC,deleted WHERE SC.CourseID = deleted.ID --测试用例 DELETE FROM Courses WHERE ID=10 --执行结果 SELECT * FROM Students SELECT * FROM Courses SELECT * FROM SC --Instead Of触发器：删除课程时,同时删除该课程的选课信息 IF OBJECT_ID (N'TRIGER_Courses_Instead_Delete', N'tr') IS NOT NULL DROP TRIGGER TRIGER_Courses_Instead_Delete; GO CREATE TRIGGER TRIGER_Courses_Instead_Delete ON Courses Instead Of DELETE AS declare @courseId int --获取要删除的课程ID SELECT @courseId=ID FROM deleted --删除选课信息 DELETE FROM SC WHERE CourseID = @courseId --删除课程信息 DELETE FROM Courses WHERE ID=@courseId --测试用例 DELETE FROM Courses WHERE ID=10 --执行结果 SELECT * FROM Students SELECT * FROM Courses SELECT * FROM SC
版权声明：
本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。
本文网址：https://www.mushiming.com/mjsbk/9985.html