SQL SERVER复习笔记06

--十二.存储过程
--创建存储过程
create procedure book1_pr1
as
select book1.书名 from book1

--执行存储过程
execute book1_pr1

--查看存储过程
sp_helptext book1_pr1

--带输入参数的存储过程
create proc book1_pr2
@书名 char(20)
as
select * from book1
where 书名=@书名

--执行带参存储过程
exec book1_pr2 @书名='sql数据库'

--带输出参数的存储过程,指定OUTPUT关键词 
create proc book1_pr3
@书名 char(20), @book1NUM smallINT output
as
set @book1NUM=
(
select COUNT(*) from book1
where 书名=@书名
)
print @book1NUM

--执行时,输入参数要赋值,面输出参数不用赋值
declare @书名 char(20), @book1NUM smallINT
set @书名='sql数据库'
exec book1_pr3 @书名,@book1NUM

--修改,加密,重新编译存储过程
alter proc book1_pr1
@出版社 varchar(20)
/*with encryption*/ 
with recompile  --在定义时重新编译
as
select * from book1
where 出版社=@出版社

declare @出版社 varchar(20)
set @出版社='中山大学'
exec book1_pr1 @出版社

--执行时重新编译
declare @出版社 varchar(20)
set @出版社='中山大学'
exec book1_pr1 @出版社 with recompile

--通过系统存储过程重新编译
exec sp_recompile book1_pr1

--十三.触发器,insert/delete/update
--创建触发器
create trigger book1_tr1
on book1
for insert  --触发器和触发语句都会执行
as
print '插入成功'

insert into book1 values ('134472','172','sql数据库',35,'中山大学','03-13-2008')

--触发器执行,但触发语句不执行,用INSTEAD OF
create trigger book1_tr2
on book1
instead of delete
as
print '不能删除!'

delete from book1  --测试一下

select * from book1  --发现表中数据并没有删除

--if update(column_name),不用INSTEAD OF,而是用ROLLBACK TRANSACTION来回滚
create trigger book1_tr3
on book1
for update
as
if update(定价)
begin
rollback transaction
end

--测试一下
update book1
set 定价=45
where 编号='101172'
/*
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。
*/

--管理触发器
--1.sp_help trigger_name,了解触发器的一般信息,如名称,创建时间等
exec sp_help book1_tr3

--2,sp_helptext trigger_name,查看TRIGGER的定义信息
exec sp_helptext book1_tr3

--3,sp_depends trigger_name | table_name,查看触发器所引用的表或表涉及的所有触发器和存储过程
exec sp_depends book1_tr3

exec sp_depends book1
--4,sp_helptrigger table_name,查看表所涉及所有触发器的信息
exec sp_helptrigger book1

--5，通过系统表来查看触发器的信息
select *
from sysobjects
where type='tr'

--6,禁止触发器
alter table book1
disable trigger trigger_name | all

--启用触发器
alter table book1
enable trigger trigger_name | all

学习教材下载