SQL SERVER 存储过程和触发器

 

------存储过程练习(不带输出值)------

select * from T_person4

create procedure pro_T_proson4   -----简单分页效果
@pageindex int=1,    -------输入参数
@pagenum int=5      -------输入参数
as
select top (@pagenum) * from ---要用(把参数括起来)  
(
   select Row_number() over (order by id) as r, * from T_person4 
) as p
where p.r>(@pageindex-1)*@pagenum and p.r<=@pageindex*@pagenum

exec pro_T_proson4 ---不传值是调用的是默认值
exec pro_T_proson4 2,5 -----传入值

---------------存储过程练习（带输出值）-----------------
alter procedure  pro_T_person
@pageindex int=1,
@pagenum int=3,
@countnum int output,
@pagecount int output
as  ----语句都放在as 后面执行
select @countnum=count(*),@pagecount=ceiling((count(*)+0.0)/@pagenum) from T_person4
select top (@pagenum) * from ---要用(把参数括起来)  
(
   select Row_number() over (order by id) as r, * from T_person4 
) as p
where p.r>(@pageindex-1)*@pagenum and p.r<=@pageindex*@pagenum

declare @countnum int,@pagecount int
exec pro_T_person 1,4,@countnum output,@pagecount output
print @countnum
print @pagecount

 

------------触发器的使用------------
-----inserted表和deleted表的作用
-----inserted表是一个逻辑表，结构和触发表完全一致
-----银行取款时，先要看用户的存款金额是否有取款金额大。
select * from deposit
select * from payment
create trigger tt
on payment
for insert
as
declare @depositor_id char(4)
declare @fund money
select @depositor_id=depositor_id from inserted
select @fund=fund from inserted
begin
   if @fund<(select fund from deposit where depositor_id=@depositor_id)
     update deposit set fund=fund-@fund where depositor_id=@depositor_id
    else
      begin
      print '存款不足'
      rollback transaction  ----回滚事务
      end 
end



insert into payment(depositor_id,depositor_name,fund)values('1234','李dd',50090)

-----当毕业后的学生在学生表中删除，并插入到毕业生表中------------
create trigger stu_deleted
on stu_info
for delete
as
insert into old_stu select * from deleted

select * from book

------------用触发器来限定不能修改的列---------------
------------主要用到了rollback Transaction----------
create trigger book_update
on book
for update
as
if update(B_Name)
 begin
 raiserror('图片名称不能修改',10,1)
 rollback Transaction
 end