存储过程是存储在服务器上的一段Transact-SQL语句的集合。
系统存储过程存储在master数据库中并以sp_开头;
用户定义存储过程:输出参数不能是text、ntext、image
create procedure lyy
@li int output,@yang nvarchar(20)='liyang'
as
begin
……
end
执行存储过程:
declare @ly int
execute lyy @ly output
修改存储过程:
alter procedure lyy
@li int output,@yang nvarchar(20)='liyang' ,@liyy nvarchar(20)
with encryption
as
begin
select @li=Id,name from lyy where city=@yang and company=@company
end
删除存储过程:
drop procedure lyy
触发器也是SQL语句集,与存储过程的区别是触发器不能用execute调用,而是在满足条件的时候自动触发,主要作用是用来保证数据表的参照完整性。
创建基于表的触发器:
create trigger tr_lyy on lyy after insert,update,delete
as
insert into liyy select userid,username from inserted
创建基于数据库的触发器:
create trigger db_delete on database after drop_table
as
print 'error!'
禁用触发器嵌套:
execute sp_configure 'nested triggers',0
启用触发器嵌套:
execute sp_configure 'nested triggers',1
查看信息:同样适用与存储过程
sp_helptext、sp_help、sp_depends
修改触发器:
alter trigger tr_lyy on lyy after insert,update,delete
as
insert into liyy select userid from inserted
禁用触发器:
alter table lyy disable trigger tr_lyy
启用触发器:
alter table lyy enable trigger tr_lyy
删除触发器:
drop trigger tr_lyy