8.存储过程和触发器
现在使用的数据库不再是之前的员工管理的数据库了，已经更改为学校上机实验使用的 学生成绩管理数据库，现附上生成学生成绩管理数据库的sql代码。使用该数据库，需要修改下路径。
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%)
GO
ALTER DATABASE [student] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [student].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [student] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [student] SET ANSI_NULLS OFF
GO
ALTER DATABASE [student] SET ANSI_PADDING OFF
GO
ALTER DATABASE [student] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [student] SET ARITHABORT OFF
GO
ALTER DATABASE [student] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [student] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [student] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [student] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [student] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [student] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [student] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [student] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [student] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [student] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [student] SET DISABLE_BROKER
GO
ALTER DATABASE [student] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [student] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [student] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [student] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [student] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [student] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [student] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [student] SET RECOVERY SIMPLE
GO
ALTER DATABASE [student] SET MULTI_USER
GO
ALTER DATABASE [student] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [student] SET DB_CHAINING OFF
GO
ALTER DATABASE [student] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [student] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [student]
GO
/****** Object: Table [dbo].[course] Script Date: 2018/5/10 17:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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]
GO
INSERT [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]
GO
ALTER 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 scinsert
on sc
after insert
as
if(select status from course,inserted where course.cno=inserted.cno)='1'
begin
print '该课程正在准备中'
print '该次选课操作失败'
rollback transaction
endstep2:插入一行状态为1的数据（在准备中）
insert into course (cno,cname,status)
values('00010','界面设计','1')step3:向sc表中插入课程号正在准备的数据
insert into sc(sno,cno) values ('03001','00010')update course
set status='0'
where cno='00010'
