存储过程和触发器
存储过程
-- SQL Server提供了一种方法，它可以将一些固定的操作集中起来
-- 由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。
-- 在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。
-- 存储过程的优点:
-- 1.共享应用程序逻辑
-- 2.屏蔽数据库细节
-- 3.提供安全机制
-- 4.提高性能
-- 5.降低网络负荷
-- =================================================================
-- 语法规则:
-- create proceduer|proc 存储过程名称,参数,参数
-- as
-- begin
-- 过程语句快
-- end
-- 注意:1.参数分为输入参数和输出参数两种,输入参数提供过程语句块使用
-- 输出参数即返回值
-- 2.参数在存储过程中也是可选项,可有可无
-- ===================================================================
-- Author: Mc
-- Create date: 2011.08.02
-- Description: 获得用户表中的所有信息
-- =============================================
CREATE PROCEDURE GetUserInfo_Proc
AS
BEGIN
select * from usertb
END
GO
--执行存储过程
execute GetUserInfo_Proc
--修改存储过程
alter proc GetUserInfo_Proc
as
begin
select username from usertb
end
go
--删除存储过程
drop proc GetUserInfo_Proc
-- ======================================================
-- Author: Mc
-- Create date: 2011.08.02
-- Description: 分页显示信息,动态表名.页号.页大小.当前页
-- 返回的内容:总页数.
-- ======================================================
CREATE PROCEDURE getpageinfo_proc
-- 当前页号
@pageNum int,
-- 页大小
@pageSize int,
-- 表名
@tableName varchar(20),
-- 表中唯一的字段
@uniqid varchar(30),
-- 输出,总的数据条数
@pageTotal int output
AS
declare @pageCount int,
--SQL语句
@sql varchar(200),
@total int ,
@count int
BEGIN
set @sql ='select count(*) from '+ @tableName
--创建临时表
create table temp(total int )
insert into temp exec(@sql)
--取出数据总数
select @pageTotal = total from temp
--删除临时表
drop table temp
if @total%@pageSize=0
set @pageCount = @total/@pageSize
else
set @pageCount = @total/@pageSize+1
--当用户输入的页码小于的时候,返回第一页
if @pageNum<1
set @pageNum=1
--拼凑要执行的SQL语句
set @sql = 'select top ('+cast(@pageSize as varchar(30)) + ') * from '+ @tableName
+ ' where '+@uniqid+‘ not in (select top ('+ cast(((@pageNum-1)*@pageSize)as varchar(20))+') '+
+@uniqid + from '+@tableName+')'
print @sql
--执行语句
exec(@sql)
END
GO
-- 执行存储过程
declare @total int
exec getpageinfo_proc 5,10,'usertb','userid' ,@total
print @total
-- ======================================================
-- 该存储过程是一个对前面的基础的一个小的总结,其中也涉及到了动态
-- SQL语句的执行,然后就是SQL语句的一个拼装.主要是因为不能在存储过
-- 程中直接执行传入的动态表名,所以需要用到这个,然后不能获得其总的
-- 数据条数即不能:select count(*) from 表名.因此也需要创建一个临时
-- 表来返回一个总的条数.后面是对分页的一个运算,应该很容易看懂.
-- ======================================================
触发器
--触发器是定义在特定的表上，与表相关
--自动触发执行
--不能直接调用
--是一个事务(可回滚）
--触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
--触发器通常用于强制业务规则
--触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束
--可执行复杂的SQL语句（if/while/case）
--可引用其它表中的列
-- 创建一张日志表
-- 针对用户对表的操作(增删改),写入不同的日志信息
create table usertb_log_tb
(
logid int identity(1,1),
userid int,
tm datetime
)
--针对删除操作所创建的触发器
-- 利用游标,在用户删除多行数据的时候,也可以正常记录日志
create trigger logoper_deltrig on usertb after delete
as
declare @userid int
declare usertb_cursor cursor for select userid from deleted
open usertb_cursor
fetch next from usertb_cursor into @userid
while @@fetch_status=0
begin
print '删除的ID是'+ cast(@userid as varchar(20))
insert into usertb_log_tb values(@userid, getdate(),'删除')
fetch next from usertb_cursor into @userid
end
close usertb_cursor
deallocate usertb_cursor
-- ===============================
-- 针对修改操作所创建的触发器
-- 修改可以看作是先删除,后添加
-- ===============================
create trigger logoper_uptrig on usertb after update
as
declare @oldname varchar(50),
@newname varchar(50)
begin
select @oldname=username from deleted
print '原用户名是:'+@oldname
select @newname=username from inserted
print '新用户名是:'+@newname
end
go
-- ===============================
-- 针对插入操作所创建的触发器
-- ===============================
create trigger logoper_instring on usertb after insert
as
declare @id int
declare @username varchar(50)
begin
select @id =userid, @username=username from inserted
print '新增的信息为:userid:'+ cast(@id as varchar(20))+' username:'+@username
insert into usertb_log_tb values(@id, getdate(),'增加')
end
go