触发器、游标

一、触发器

    触发器的两张虚拟表:

    

     Tickets表原始数据：

     

     TicketsOrders表原始数据：

     

   1.插入触发器

      创建：

create trigger ticketsOrdersInsert
 on edisondb..ticketsOrders
 after insert
 as
 begin
 if (select t.currentAmount from tickets as t,inserted
    where t.ticketid=inserted.ticketid)<
    (select amount from inserted) 
 begin
    print '订票数超过库存数，无法订票！'
    rollback transaction   -----将插入操作进行回滚
  end
 else
    print '订票成功！'
    update tickets set tickets.currentAmount=tickets.currentAmount-inserted.amount
    from inserted 
    where tickets.ticketid=inserted.ticketid
 end

     插入非法值：

use edisondb;

 insert into ticketsOrders(custid ,ticketid ,amount)
     values(10001,1,100);  

    执行结果：

   2.更新触发器

     创建： 

create trigger ticketsUpdate
on edisondb..Tickets
after update
as
begin
 if update(totalAmount)
 begin
   if (select totalAmount from deleted)<>(select currentAmount from deleted)
   begin
     raiserror('该票已经有人订票，不可更新总票数',16,1)
     rollback transaction----回滚
   end
 end
end

    更新非法数据： 

use edisondb;
update Tickets set tickets.totalAmount=100 
      where ticketid=1;

   执行结果为： 

   3.删除触发器

      创建：

create trigger ticketsDelete
on edisondb..Tickets
after delete
as
begin
if (select totalAmount from deleted)<>(select currentAmount from deleted)
begin
     print '该票已经有人订购，不可删除'
     rollback transaction----回滚
end  
end

      非法删除数据： 

use edisondb;
delete from tickets 
where ticketid=1;

      执行结果为： 

二、游标

     游标：行级操作，性能杀手

     SQL查询：数据集操作，速度快（优先选用）

    

     使用游标进行操作：（查出每个用户的总运费）

set nocount on;
use edisondb;
declare @startDT datetime
set @startDT=getdate()
declare @CustomerTotalFreight table
(
  custid numeric(18,0),
  totalFreight numeric(18,2)
);
declare
  @currentCustid as numeric(18,0),
  @prvCutstid as numeric(18,0),
  @currentFreight as numeric(18,2),
  @totalFreight as numeric(18,2);
declare customerTotalFreight_cursor cursor for  ----声明游标
    select custid,freight
    from orders
    order by custid;
open customerTotalFreight_cursor  ----打开游标
                                  ----循环获取每行记录
fetch next from customerTotalFreight_cursor into @currentCustid,@currentFreight;
select @prvCutstid=@currentCustid,@totalFreight=0.00;
while @@fetch_status=0
begin
 if @prvCutstid<>@currentCustid
 begin
   insert into @CustomerTotalFreight values(@prvCutstid,@totalFreight);
   select @prvCutstid=@currentCustid,@totalFreight=@currentFreight;
 end
 else
 begin
   set @totalFreight=@totalFreight+@currentFreight;
 end
 fetch next from customerTotalFreight_cursor into @currentCustid,@currentFreight;
end;
  insert into @CustomerTotalFreight values(@prvCutstid,@totalFreight);
close customerTotalFreight_cursor; ----关闭游标

deallocate customerTotalFreight_cursor;----释放游标

select * from @CustomerTotalFreight order by custid;

print '耗时：'+replace(str(datediff(ms,@startDT,getdate())),' ','')+' 毫秒'

   执行结果为：

  

   以上功能如果用SQL查询来实现则十分简洁：

use edisondb;
declare @startDT datetime
set @startDT=getdate()
select custid,sum(freight) as totalFreight
from orders
group by custid;
print '耗时：'+replace(str(datediff(ms,@startDT,getdate())),' ','')+' 毫秒'

   执行结果为：