存储过程、触发器、数据完整性

By TreeDream

存储过程

将需要多次调用以实现某个特定任务的代码段编成一个过程

创建存储过程

create procedure exp1asselect *from StockGo?exec exp1

带参数的存储过程

if exists (select name from sysobjects where name =‘exp2‘ and type = ‘P‘)drop procedure exp2Gocreate procedure exp2 @mno char(8),@mname varchar(50),@mspeci varchar(20)asinsert into Stock(mat_num,mat_name,speci)values(@mno,@mname,@mspeci)GO?execute exp2 ‘m030‘,‘护套绝缘电线‘,‘BVV-35‘

带默认输入参数的存储过程

if exists (select name from sysobjects where name = ‘exp3‘ and type = ‘P‘)drop procedure exp3GOcreate procedure exp3 @mname varchar(50) = ‘%绝缘%‘,@pno char(8) = ‘20110005‘asselect mat_name,speci,prj_name,prj_statusfrom Stock,Salvaging,Out_stockwhere Stock.mat_num = Out_stock.mat_num and Salvaging.prj_num = Out_stock.prj_num and mat_name like @mname and Salvaging.prj_num = @pnoGO?execute exp3execute exp3 @pno = ‘20110002‘

带输出参数的存储过程

if exists (select name from sysobjects where name = ‘exp4‘ and type = ‘P‘)drop procedure exp4GOcreate procedure exp4 @pn char(8),@sum int outputasselect @sum = sum(amount)from Out_stockwhere prj_num = @pnGO?declare @total intexecute exp4 ‘20110002‘,@total outputprint ‘总量‘+ cast(@total as varchar(20))

修改存储过程

修改和创建相同alter

删除存储过程

drop procedure exp4

触发器

当用户进行插入，删除，更新等数据操作的时候，自动触发所定义的SQL语句

创建触发器

insert触发器，同时将数据复制到基本表和内存中的Inserted表中

if exists (select name from sysobjects where name = ‘tr1_stock‘ and type = ‘P‘)drop trigger tr1_stockGOcreate trigger tr1_stock on Stockafter insertasdeclare @amount intselect @amount = amountfrom insertedif @amount < 1Beginrollback tranraiserror (‘amount must be greater than 1!‘,16,10)ENDGO

create trigger tr1_outstockon Out_stockafter insertasBegindeclare @m_num char(8),@m_amount intselect @m_num = mat_num,@m_amount = amountfrom insertedupdate stockset amount = amount - @m_amountwhere mat_num = @m_numENDGO

delete触发器：将删除的数据保存于deleted中，SQL语句中还可以引用

create trigger tr2_stockon Stockafter deleteasBegindeclare @mat_num char(8)select @mat_num = mat_numfrom deleteddeletefrom Out_stockwhere mat_num = @mat_numEND

update触发器：合并了inserted deleted 触发器

create trigger tr3_stockon Stockafter updateasdeclare @amount_new int,@amount_old int,@mat_num char(10)select @amount_new = amount,@mat_num = mat_numfrom insertedif @amount_new < 1Beginselect @amount_old = amountfrom deletedupdate Stockset amount = @amount_oldwhere mat_num = @mat_numEND

数据完整性

约束

• 建表的时候：列级完整性约束，表级完整性约束

• alter table Salvagingadd constraint PK_salvaging primary key(prj_num)?alter table Salvagingadd constraint data_check check(start_date<=end_date)?alter table Salvagingadd constraint DF_salvaging default (0) for prj_status?alter table Salvagingdrop constraint data_check

默认值

默认值对象独立于表

创建默认值

create default _Getdateas getdate()

绑定默认值

sp_bindefault _Getdate, ‘Out_stock.get_date‘

解除默认

sp_unbindefault _Getdate

规则

check约束是在create table中建立的，规则独立于表

创建规则

create rule amount_ruleas@amount>0 and @amount<=100

绑定和解除规则,删除

create rule amount_ruleas@amount>0 and @amount<=100?sp_bindrule amount_rule,‘Stock.amount‘sp_unbindrule amount_rule,‘Stock.amount‘?drop rule amount_rule

存储过程、触发器、数据完整性