Sqlserver学习笔记
-----------------------接Part 6-------------------
29存储过程和触发器
存储过程由一组预先编辑好的SQL语句组成。将其放在服务器上。由用户通过指定存储过程的名称来执行。
触发器是一种特殊类型的存储过程，它不是由用户直接调用的，而是当用户对数据进行操作（包括数据的INSERT ,UPDATE,DELETE操作）时自动执行。
29.1创建、执行、删除简单的存储过程。
CREATEPROCEDURE存储过程名
[@parameter data_type][= default][output][,…..]
AS
sql语句
29.2执行存储过程
对存储在服务器上的存储过程，可以使用exectue命令或者其名称执行
29.3查看存储过程
对用户建立存储过程，可以使用对象资源管理器或系统存储过程查看该存储过程的定义。
1， 使用对象资源管理器查看存储过程，在“对象资源管理器”窗口中，展开“数据库”结点，选择相应的数据库，展开“可编程性”，“存储过程”结点。
2， 使用系统存储过程查看存储过程，可以使用SP_HELPTEXT SP_DEPENDS SP_HELP
29.4删除存储过程
可以利用资源管理器或者drop proc语句将其删除
示例
--存储过程
CREATEPROCEDURE pro
AS
SELECT学生.学号,学生.姓名,班级.班级名称,专业.专业名称,系部.系部名称
FROM学生
JOIN班级ON学生.班级代码=班级.班级代码
JOIN专业ON班级.专业代码=专业.专业代码
JOIN系部ON班级.系部代码=系部.系部代码
--上述语句的执行后，我们可以在[可编程性\存储过程]看到一个名为dbo.pro的存储过程
EXECpro
--创建带参数的一个存储过程
CREATEPROC pro1
@sex VARCHAR(10),--注意这里有逗号
@id VARCHAR(10)
AS
SELECT学生.学号,学生.姓名,班级.班级名称,专业.专业名称,系部.系部名称
FROM学生
JOIN班级ON学生.班级代码=班级.班级代码AND学生.性别=@sex
JOIN专业ON班级.专业代码=专业.专业代码
JOIN系部ON班级.系部代码=系部.系部代码AND系部.系部代码=@id
--执行存储过程
EXECpro1 '男','1'
--带输出参数的存储过程
CREATEPROC pro2
@setName VARCHAR(10),
@getNum VARCHAR(10)OUTPUT
AS
SELECT@getNum =学号FROM学生WHERE姓名=@setName
--执行
----该语句的执行，看不到结果，但是如果用.net等编程语言调用时则会看到效果
EXECpro2 '林姐','1'
--查看存储过程
EXECSP_HELPTEXT pro1
EXECSP_DEPENDS pro1
EXECSP_HELPpro1
--删除存储过程
DROPPROC pro1
29.5触发器
触发器的优点：
1触发器自动执行，在对表中的数据做了任何修改之后立即被激活
2触发器能够对数据库中的相关表实现级联更改。
29.6临时表
INSERTED表和DELETED表用于存放对表中数据行的修改信息。他们是触发器执行时自动创建的。当触发器工作完成，他们也被删除。他们只是只读表，不能向他们写入内容。
INSERTED表：用来存储INSERT和UPDATE语句所影响的行的副本。
意思就是在INSERTED表中临时保存了被插入或被更新后的记录行。
在执行INSERT或UPDATE语句时，新加行被同时添加到INSERTED表和触发器表中。
DELETED表：用来存储DELETE和UPDATE语句所影响的行的副本。
意思是在DELETED表中临时保存了被删除或被更新前的记录行。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传到DELETED表中。所以可以从deleted表中检查删除的数据行是否能删除。
所以当表中某条记录的某项值发生变化时，变化前的值已经通过系统自动创建的临时表DELETED表和INSERTED表保存了被删除行或插入的记录行的副本。我么可以从这两个表中查询出变化前的值并赋给变量。
29.7使用sql创建触发器
格式：
CREATETRIGGERtrigger_name
ON{TABLE | view}
FOR{[INSERT],[DELETE],[UPDATE]}
AS
sql语句
查看触发器信息
EXECsp_helptrigger表名
删除触发器
DROP TRIGGER触发器名称
示例
USEstudent --在数据库student下的操作。。
GO---这个词不能少
--创建触发器
CREATETRIGGER tri_insert
ONstd_TABLE ---指定目标表
FORINSERT---触发器类型
AS--别忘了这个
DECLARE@a CHAR(20), @name CHAR(20), @major CHAR(20)
--从临时表INSERTED中获取数据，数据保存在定义的变量中
--INSERTED保存着被插入或更新后的数据
SELECT@a =学号,@name =姓名,@major =专业方向FROMINSERTED
--删除时的对应操作，把获取的数据插入另一表中
INSERTINTO std_table2(学号,名称,专业方向)VALUES(@a, @name, @major)
--执行上述语句后，我们可以在触发器 结点下看到一个tri_insert
--测试
SELECT*FROM std_table
--SELECT * FROM std_table2--此时出错，std_table2没有建立
--删除触发器
DROPTRIGGER tri_insert
--重新创建
USEstudent --在数据库student下的操作。。
GO---这个词不能少
--上述语句的执行--选定数据库，和试图界面下手动选定一个样
--创建触发器
CREATETRIGGER tri_insert
ONstd_TABLE ---指定目标表
FORINSERT---触发器类型
AS--别忘了这个
DECLARE@a CHAR(20), @name CHAR(20), @major VARCHAR(20)--注意这边貌似只能这样写，多个变量一起定义
--从临时表INSERTED中获取数据，数据保存在定义的变量中
--INSERTED保存着被插入或更新后的数据
SELECT@a =学号,@name =姓名,@major =专业方向FROMINSERTED
--删除时的对应操作，把获取的数据插入另一表中
INSERTINTO std_table1(学号,姓名,专业方向)VALUES(@a, @name, @major)
--测试
SELECT*FROM std_table
SELECT*FROM std_table1
--查看触发器的信息
execsp_helptrigger std_table
