转！！存储过程和触发器
1. 存储过程和触发器是什么?
存储过程不能可以显著提高系统的执行速度,还能提高效率确保一致性.
1.1存储过程:一种数据库对象,将负责不同功能的语句分类放置起来,以便能反复使用.
1.2特点:
1.3存储过程的分类
分为五类,系统存储过程,用户定义存储过程,临时存储过程,远程存储过程,扩展存储过程.
临时存储过程又分为本地临时存储过程,全局临时存储过程.
|
分类 |
说明 |
|
系统存储过程 |
存储在master中,以sp开头,调用时不必加库名,如果参数是保留字或者数据库对象,用单引号包围. |
|
用户定义存储过程 |
用户为完成一定的功能定义在数据库中存储过程. |
|
临时存储过程 |
本地临时:#开头,放在tempdb中,连接断开之后自动删除,本库使用. |
|
全局临时:##开头,放在tempdb中,连接断开后使用完之后自动删除,本连接的所有库可以使用.注意命名. |
|
|
远程存储过程 |
位于远程服务器上的存储过程.通过分布式查询和execute执行. |
|
扩展存储过程 |
外部程序写的存储过程,xp开头,动态链接库形式存在,也放在master |
1.4存储过程优点
1.5存储过程和视图的比较
1.6创建存储过程
格式:
例子:
create procedure ShowAllStudent
as
begin
select * from students
end
1.7执行存储过程
格式:exec procedure_name
例子:exec ShowAllStudent
1.8带输入参数存储过程
格式:
例子:
create procedure SelectStudentByStu_no
@sno char(12)='200501020319'
as
begin
select * from students where stu_no=@sno
end
1.9执行带输入参数的存储过程
格式:
例子:
exec SelectStudentByStu_no
@sno='200501030218'
当参数比较少的时候,可以按照位置传递参数.
例子:
exec SelectStudentByStu_no '200501030218'
1.10带输出参数的存储过程
格式:
例子:
create procedure GetStudentCountByStu_sex
@sex char(2)='男',
@count int=0 output
as
begin
set
@count=(select count(*) from students where stu_gender=@sex)
print @count
end
执行带输出参数的存储过程
例子:
declare @sex char(2) ,@count int
set @sex='男'
exec GetStudentCountByStu_sex @sex,@count
1.11 删除存储过程
Drop procedure GetPostsByBlogId
1.12 更新存储过程
alter procedure GetPostsByBlogId
(@blogid varchar(50))
As
Select top 5* from Posts where BlogId=@blogid
1.13 重命名存储过程
对象浏览器中修改.
1.14 重新编译存储过程
有三种方法:
1)创建的时候使用with Recompile 语句.
2)在执行过程中设定重新编译
3)调用系统存储过程重新编译
2. 触发器
基本表被在修改的时候通过事件触发而执行的存储过程.
作用是保证了由主键和外键所不能保证的参照完整性和数据完整性.
触发器原理：
触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。
触发器的作用：
触发器的主要作用是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。它能够对数据库中的相关表进行级联修改，强制比CHECK约束更复杂的数据完整性，并自定义操作消息，维护非规范化数据以及比较数据修改前后的状态。与CHECK约束不同，触发器可以引用其它表中的列。在下列情况下使用触发器实现复杂的引用完整性；强制数据间的完整性。创建多行触发器，当插入，更新、删除多行数据时，必须编写一个处理多行数据的触发器。执行级联更新或级联删除这样的动作。级联修改数据库中所有相关表。撤销或者回滚违反引用完整性的操作，防止非法修改数据。
触发器与存储过程的区别：
触发器与存储过程的主要区别在于触发器的运行方式。存储过程必须有用户、应用程序或者触发器来显示的调用并执行，而触发器是当特定时间出现的时候，自动执行或者激活的，与连接用数据库中的用户、或者应用程序无关。当一行被插入、更新或者删除时触发器才执行，同时还取决于触发器是怎样创建的，当UPDATE发生时使用一个更新触发器，当INSERT发生时使用一个插入触发器，当DELETE发生时使用一个删除触发器。
2.1触发器的优点
触发器可以包含复杂的处理逻辑,主要用来保持低级的数据完整性.优点如下.
2.2创建触发器
删除触发器:
例子:
create trigger dropStudent
on students
for delete
as
print '成功删除一条数据.'
更新触发器:
create trigger updateStudentName
on students
for update
as
if update (stu_name)
begin
print '不能更新主键,学生号码.'
rollback transaction
end
update students set
stu_name='五哥'
where stu_no='200501020319'
删除触发器:略
查看触发器:
1) 使用系统的存储过程查看: exec sp_helptrigger students
使用系统表: select name from sysobjects where type='TR'
2) 管理器查看 略
删除触发器: drop trigger updateStudent
修改触发器:
重命名: sp_rename dropStudent,deleteStudentItem
启动和停止触发器:
alter table students enable trigger all
