数据库系列三

数据库（存储过程、触发器）

一、存储过程
1.创建存储过程
CREATE PROC[EDURE] 存储过程名
[ { @parameter data_type } [ INPUT|OUTPUT ]
] [ ,…n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
AS
SQL 语句
调用：
格式一：exec[ute] procedure_name 实参1，实参2 ……
格式二：exec[ute] procedure_name 形参1=实参1，形参2=实参2 ……

例：例7-2] 在Teach数据库中，创建一个名称为InsertRecord的存储过程，该存储过程的功能是向Studinfo数据表中插入一条记录，新记录的值由参数提供。
CREATE PROCEDURE InsertRecord
@sno char(6),@sn char(20),@sex char(2), @b smalltdatetime, @dept char(10)
AS
begin
if not exists(select * from Studinfo where sno=@sno)
INSERT INTO Studinfo
VALUES(@sno,@sn,@sex,@d,@dept)
else
print ‘该学号已存在’
end
调用：exec insertRecord ‘101’,‘王芳’, 0,‘1999-2-1’,‘信工院’

二、触发器

1.触发器有4个要素：
名称：触发器有一个符合标志符命名规则的名称。
定义的目标：触发器必须定义在表或者视图上。
触发条件：表名相同，操作相同。
触发逻辑：触发之后如何处理。

2.触发器的种类
（1）AFTER触发器 ：AFTER触发器是告诉SQL语句执行了INSERT、UPDATE或者DELETE操作后干什么。
（2）INSTEAD OF触发器 ：告诉当要执行INSERT、UPDATE或DELETE操作时用什么别的操作来代替。

3.工作原理
SQL Server在工作时为每个触发器在服务器的内存上建立两个特殊的表：插入表（inserted）和删除表(deleted)。这两张表的结构与作用的表的结构完全一样，当作用表的SQL 语句开始时，自动产生这两张表，当触发器执行完毕，这两张表也随即删除。只读。
（1）插入表的功能
一旦对该表执行了插入（INSERT）操作，那么对该表插入的所有行来说，都有一个相应的副本存放到Inserted表中，即Inserted表用来存储原表插入的内容。
（2）删除表的功能
一旦对该表执行了删除（DELETE）操作，则将所有的删除行存放至Deleted表中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从Deleted表中得以还原。

4.创建触发器
（1）在创建触发器以前必须考虑到以下几个方面：
①CREATE TRIGGER语句必须是批处理的第一个语句。
②触发器是数据库对象，所以其命名必须符合命名规则。
③触发器只能创建在当前数据库中。
④虽然在视图上可以建触发器，但要受到很多的限制，因此不建议在VIEW上建触发器。
⑤一个触发器只能对应一个表，这是由触发器的机制决定的。

（2）创建
CREATE trigger 触发器名
On 表名/视图名
Instead of/after/for{insert/update/delete}
As
SQL语句

例：
①创建一个触发器，当向 Studinfo表做插入操作时，自动显示提示信息。
CREATE TRIGGER ChangeDisplay
ON Studinfo
FOR INSERT
AS
print ‘正在对studinfo表进行插入操作’

②创建一个触发器trgup,保证studinfo表中的性别只能是0，1.
if exists(select * from sysobjects where name=‘trgup’ and type=‘tr’)
drop trigger trgup
go
create trigger trgup
on studinfo
for insert,update
as
if exists(select *from inserted where ssex not in (0,1))
rollback
else
print ‘允许操作’