USE [master]GO/****** Object: Database [student] Script Date: 2018/5/10 17:13:01 ******/CREATE DATABASE [student] CONTAINMENT = NONE ON PRIMARY ( NAME = N'student', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\student.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'student_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\student_log.ldf' , SIZE = 1024KB , MAXSIZE = 5120KB , FILEGROWTH = 10%)GOALTER DATABASE [student] SET COMPATIBILITY_LEVEL = 110GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))beginEXEC [student].[dbo].[sp_fulltext_database] @action = 'enable'endGOALTER DATABASE [student] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [student] SET ANSI_NULLS OFF GOALTER DATABASE [student] SET ANSI_PADDING OFF GOALTER DATABASE [student] SET ANSI_WARNINGS OFF GOALTER DATABASE [student] SET ARITHABORT OFF GOALTER DATABASE [student] SET AUTO_CLOSE OFF GOALTER DATABASE [student] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [student] SET AUTO_SHRINK OFF GOALTER DATABASE [student] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [student] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [student] SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE [student] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [student] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [student] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [student] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [student] SET DISABLE_BROKER GOALTER DATABASE [student] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [student] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [student] SET TRUSTWORTHY OFF GOALTER DATABASE [student] SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE [student] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [student] SET READ_COMMITTED_SNAPSHOT OFF GOALTER DATABASE [student] SET HONOR_BROKER_PRIORITY OFF GOALTER DATABASE [student] SET RECOVERY SIMPLE GOALTER DATABASE [student] SET MULTI_USER GOALTER DATABASE [student] SET PAGE_VERIFY CHECKSUM GOALTER DATABASE [student] SET DB_CHAINING OFF GOALTER DATABASE [student] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GOALTER DATABASE [student] SET TARGET_RECOVERY_TIME = 0 SECONDS GOUSE [student]GO/****** Object: Table [dbo].[course] Script Date: 2018/5/10 17:13:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[course]( [cno] [nchar](10) NULL, [cname] [nchar](10) NULL, [credit] [nchar](10) NULL, [pcno] [nchar](10) NULL) ON [PRIMARY]GO/****** Object: Table [dbo].[sc] Script Date: 2018/5/10 17:13:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[sc]( [sno] [nchar](10) NULL, [cno] [nchar](10) NULL, [grade] [int] NULL) ON [PRIMARY]GO/****** Object: Table [dbo].[student] Script Date: 2018/5/10 17:13:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[student]( [sno] [nchar](10) NULL, [sname] [nchar](10) NULL, [ssex] [nchar](10) NULL, [sage] [nchar](10) NULL, [sdept] [nchar](10) NULL) ON [PRIMARY]GOINSERT [dbo].[course] ([cno], [cname], [credit], [pcno]) VALUES (N'1 ', N'数据库 ', N'4 ', N'5 ')INSERT [dbo].[course] ([cno], [cname], [credit], [pcno]) VALUES (N'2 ', N'数学 ', N'6 ', NULL)INSERT [dbo].[course] ([cno], [cname], [credit], [pcno]) VALUES (N'3 ', N'信息系统 ', N'3 ', N'1 ')INSERT [dbo].[course] ([cno], [cname], [credit], [pcno]) VALUES (N'4 ', N'操作系统 ', N'4 ', N'6 ')INSERT [dbo].[course] ([cno], [cname], [credit], [pcno]) VALUES (N'5 ', N'数据结构 ', N'4 ', N'7 ')INSERT [dbo].[course] ([cno], [cname], [credit], [pcno]) VALUES (N'6 ', N'数据处理 ', N'3 ', NULL)INSERT [dbo].[course] ([cno], [cname], [credit], [pcno]) VALUES (N'7 ', N'Pascal ', N'4 ', N'6 ')INSERT [dbo].[sc] ([sno], [cno], [grade]) VALUES (N'95001 ', N'1 ', 92)INSERT [dbo].[sc] ([sno], [cno], [grade]) VALUES (N'95001 ', N'2 ', 85)INSERT [dbo].[sc] ([sno], [cno], [grade]) VALUES (N'95001 ', N'3 ', 88)INSERT [dbo].[sc] ([sno], [cno], [grade]) VALUES (N'95002 ', N'2 ', 90)INSERT [dbo].[sc] ([sno], [cno], [grade]) VALUES (N'95002 ', N'3 ', 80)INSERT [dbo].[sc] ([sno], [cno], [grade]) VALUES (N'95003 ', N'2 ', 85)INSERT [dbo].[sc] ([sno], [cno], [grade]) VALUES (N'95004 ', N'1 ', 58)INSERT [dbo].[sc] ([sno], [cno], [grade]) VALUES (N'95004 ', N'2 ', 85)INSERT [dbo].[student] ([sno], [sname], [ssex], [sage], [sdept]) VALUES (N'95001 ', N'李勇 ', N'男 ', N'20 ', N'cs ')INSERT [dbo].[student] ([sno], [sname], [ssex], [sage], [sdept]) VALUES (N'95002 ', N'刘晨 ', N'女 ', N'19 ', N'is ')INSERT [dbo].[student] ([sno], [sname], [ssex], [sage], [sdept]) VALUES (N'95003 ', N'王敏 ', N'女 ', N'18 ', N'ma ')INSERT [dbo].[student] ([sno], [sname], [ssex], [sage], [sdept]) VALUES (N'95004 ', N'张立 ', N'男 ', N'19 ', N'is ')INSERT [dbo].[student] ([sno], [sname], [ssex], [sage], [sdept]) VALUES (N'95005 ', N'流云 ', N'女 ', N'18 ', N'cs ')USE [master]GOALTER DATABASE [student] SET READ_WRITE GO 现在进入存储过程，之前的代码未保存，已经丢失，这里不重新写了，直接附上一个简单的带有输入参数的存储过程。
--带有输入参数的存储过程create procedure ssc @cno char(5) as select student.sno,sname,grade from student join sc on student.sno=sc.sno where sc.cno=@cno execute ssc '3'
接下来是触发器，触发器的功能是：向sc表中插入一行数据，这是检查对应的课程是否正在准备中（查看对应课程表再表course中的状态是否为1），如果在准备中，则不能选修。（需要提前向course表中 插入一列status(char(1))）
step1:创建触发器
create trigger scinserton scafter insertasif(select status from course,inserted where course.cno=inserted.cno)='1'beginprint '该课程正在准备中'print '该次选课操作失败'rollback transactionend
step2:插入一行状态为1的数据（在准备中）
insert into course (cno,cname,status)values('00010','界面设计','1') step3:向sc表中插入课程号正在准备的数据
insert into sc(sno,cno) values ('03001','00010')step4:更新course表中的status，重新执行step3 update course set status='0'where cno='00010'