触发器应用示例

-- 统计已有维客的帐户：
use waydu_new
go
declare @count int  
declare @total int                        --记录总数
declare @maxID int                        --最大id记录
declare @tmp_Id int
declare @tmp_memberId  varchar(36)        --用户Id 

select @total=count(*) from T_mem_memberInfo
select @maxID=max(id) from  T_mem_memberInfo
   while(@count<=@total)
      begin
         set @count=@count+1 
         select @tmp_Id=max(id) from  T_mem_memberInfo where id<isnull(@tmp_Id,@maxID+1)
         select @tmp_memberId=memberId from  T_mem_memberInfo where id=@tmp_Id
         --修改维币数：
        declare @earnWBcount   int               --净赚的维币总数  
        declare @wbIN          int               --得到的维币总数  
        declare @wbOUT         int               --消费的维币总数  
      
        declare @earnRMBcount  float             --净赚的人民币总数  
        declare @rmbIN         float    --得到的人民币总数  
        declare @rmbOUT        float    --消费的人民币总数  
      
        select @wbIN=sum(wb) from T_deal_record where  gainmemberid=@tmp_memberId   
        select @wbOUT=sum(wb) from T_deal_record where supplymemberid=@tmp_memberId  
        set @earnWBcount=isnull(@wbIN,0)-isnull(@wbOUT,0)  
      
        select @rmbIN=sum(rmb) from T_deal_record where  gainmemberid=@tmp_memberId   
        select @rmbOUT=sum(rmb) from T_deal_record where  supplymemberid=@tmp_memberId  
        set @earnRMBcount=isnull(@rmbIN,0)-isnull(@rmbOUT,0)  
        update  T_mem_memberInfo set intergral=@earnWBcount,cash=@earnRMBcount where memberId=@tmp_memberId

      end
go
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
--创建 更新、添加 触发器
use waydu_new 
go
 drop trigger  Trg_userAccountForU  
go
create trigger Trg_userAccountForU
on T_deal_record
for insert,update
 as
if update(wb) or update (rmb)
  begin
    declare @tmpsupplymemberid     varchar(36)     --提供者 
    declare @tmpgainmemberid       varchar(36)     --获得者

    declare @earnWBcount   int               --净赚的维币总数  
    declare @wbIN          int               --得到的维币总数  
    declare @wbOUT         int               --消费的维币总数  
  
    declare @earnRMBcount  float             --净赚的人民币总数  
    declare @rmbIN         float             --得到的人民币总数  
    declare @rmbOUT        float             --消费的人民币总数  
  
    select @tmpsupplymemberid=supplymemberid,@tmpgainmemberid=gainmemberid  from inserted NEW
    --统计 @tmpsupplymemberid 的帐号：
    select @wbIN=sum(wb) from T_deal_record where  gainmemberid=@tmpsupplymemberid   
    select @wbOUT=sum(wb) from T_deal_record where supplymemberid=@tmpsupplymemberid  
    set @earnWBcount=isnull(@wbIN,0)-isnull(@wbOUT,0)  
  
    select @rmbIN=sum(rmb) from T_deal_record where  gainmemberid=@tmpsupplymemberid   
    select @rmbOUT=sum(rmb) from T_deal_record where  supplymemberid=@tmpsupplymemberid  
    set @earnRMBcount=isnull(@rmbIN,0)-isnull(@rmbOUT,0)  

    --更新@tmpsupplymemberid 的帐号：
    update  T_mem_memberInfo set intergral=@earnWBcount,cash=@earnRMBcount where memberId=@tmpsupplymemberid
    -----------------------
    --统计 @tmpgainmemberid 的帐号：
    select @wbIN=sum(wb) from T_deal_record where  gainmemberid=@tmpgainmemberid   
    select @wbOUT=sum(wb) from T_deal_record where supplymemberid=@tmpgainmemberid  
    set @earnWBcount=isnull(@wbIN,0)-isnull(@wbOUT,0)  
  
    select @rmbIN=sum(rmb) from T_deal_record where  gainmemberid=@tmpgainmemberid   
    select @rmbOUT=sum(rmb) from T_deal_record where  supplymemberid=@tmpgainmemberid  
    set @earnRMBcount=isnull(@rmbIN,0)-isnull(@rmbOUT,0)  
    --更新 @tmpgainmemberid 的帐号：
    update  T_mem_memberInfo set intergral=@earnWBcount,cash=@earnRMBcount where memberId=@tmpgainmemberid

    

 end
 
go 
--创建 删除 触发器
use waydu_new 
go
 drop trigger  Trg_userAccountForD
go
create trigger Trg_userAccountForD
on T_deal_record
for delete
 as

    declare @tmpsupplymemberid     varchar(36)     --提供者 
    declare @tmpgainmemberid       varchar(36)     --获得者

    declare @earnWBcount   int               --净赚的维币总数  
    declare @wbIN          int               --得到的维币总数  
    declare @wbOUT         int               --消费的维币总数  
  
    declare @earnRMBcount  float             --净赚的人民币总数  
    declare @rmbIN         float             --得到的人民币总数  
    declare @rmbOUT        float             --消费的人民币总数  
  
    select @tmpsupplymemberid=supplymemberid,@tmpgainmemberid=gainmemberid  from deleted  OLD
    --统计 @tmpsupplymemberid 的帐号：
    select @wbIN=sum(wb) from T_deal_record where  gainmemberid=@tmpsupplymemberid   
    select @wbOUT=sum(wb) from T_deal_record where supplymemberid=@tmpsupplymemberid  
    set @earnWBcount=isnull(@wbIN,0)-isnull(@wbOUT,0)  
  
    select @rmbIN=sum(rmb) from T_deal_record where  gainmemberid=@tmpsupplymemberid   
    select @rmbOUT=sum(rmb) from T_deal_record where  supplymemberid=@tmpsupplymemberid  
    set @earnRMBcount=isnull(@rmbIN,0)-isnull(@rmbOUT,0)  

    --更新@tmpsupplymemberid 的帐号：
    update  T_mem_memberInfo set intergral=@earnWBcount,cash=@earnRMBcount where memberId=@tmpsupplymemberid
    -----------------------
    --统计 @tmpgainmemberid 的帐号：
    select @wbIN=sum(wb) from T_deal_record where  gainmemberid=@tmpgainmemberid   
    select @wbOUT=sum(wb) from T_deal_record where supplymemberid=@tmpgainmemberid  
    set @earnWBcount=isnull(@wbIN,0)-isnull(@wbOUT,0)  
  
    select @rmbIN=sum(rmb) from T_deal_record where  gainmemberid=@tmpgainmemberid   
    select @rmbOUT=sum(rmb) from T_deal_record where  supplymemberid=@tmpgainmemberid  
    set @earnRMBcount=isnull(@rmbIN,0)-isnull(@rmbOUT,0)  
    --更新 @tmpgainmemberid 的帐号：
    update  T_mem_memberInfo set intergral=@earnWBcount,cash=@earnRMBcount where memberId=@tmpgainmemberid

 
go 

-- sp_helptext '1_Proc_user_userAccount'
-- exec [1_Proc_user_userAccount] '6B212F1B-0139-432A-BC9B-C28FA99BF756'

-- select * from t_mem_memberinfo where usercode='jianbao' 
--
-- select * from t_deal_record where gainmemberid='626C826B-F9FB-41DB-A916-FB68B962EF94' 
--
-- insert into t_deal_record(resourceid,supplymemberid,gainmemberid,dealstatus,dealtype,resourcetype,wb,rmb,reason,amount,fee,dealtime)
-- select resourceid,supplymemberid,gainmemberid,dealstatus,dealtype,resourcetype,wb,rmb,reason,amount,fee,getdate() from t_deal_record where gainmemberid='626C826B-F9FB-41DB-A916-FB68B962EF94' and id=30

-- delete from  t_deal_record where id=30


-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
--创建 更新、添加 触发器

--判断该资源时候在前台页面显示1显示，0不显示

use waydu_new 
go
drop trigger  Trg_resOtherShareForU  
go
create trigger Trg_resOtherShareForU
 on T_res_otherShareCondition
for insert,update
 as
   declare @tmpresourceId  int
   declare @tmpresourceType varchar(10)
   declare @readShareTo  varchar(MAX)
   declare @DownShareTo  varchar(MAX)
   if update(readShareTo) or update (downShareTo)
     begin
       select @tmpresourceId=resourceId,@tmpresourceType=resourceType,@readShareTo=readShareTo,@DownShareTo=downShareTo  from inserted NEW
       if(@readShareTo<>''or @DownShareTo<>'')
           begin
             if(@tmpresourceType='1')
                begin
                  update T_blog set shareOtherCon=0 where id=@tmpresourceId
                end
              else if(@tmpresourceType='2')
                 begin
                   update T_user_link set shareOtherCon=0 where id=@tmpresourceId
                 end
              else if(@tmpresourceType='3')
                 begin
                   update T_file_upload set shareOtherCon=0 where fileid=@tmpresourceId
                 end
           end
       else
         if(@tmpresourceType='1')
            begin
              update T_blog set shareOtherCon=1 where id=@tmpresourceId
            end
          else if(@tmpresourceType='2')
             begin
               update T_user_link set shareOtherCon=1 where id=@tmpresourceId
             end
          else if(@tmpresourceType='3')
             begin
               update T_file_upload set shareOtherCon=1 where fileid=@tmpresourceId
             end

     end
go
--创建 更新、添加 触发器

--判断该资源时候在前台页面显示1显示，0不显示

use waydu_new 
go
drop trigger  Trg_resOtherShareForD  
go
create trigger Trg_resOtherShareForD
 on T_res_otherShareCondition
for delete
 as
   declare @tmpresourceId  int
   declare @tmpresourceType varchar(10)
   declare @readShareTo  varchar(MAX)
   declare @DownShareTo  varchar(MAX)

   select @tmpresourceId=resourceId,@tmpresourceType=resourceType,@readShareTo=readShareTo,@DownShareTo=downShareTo  from deleted OLD
   
     if(@tmpresourceType='1')
        begin
          update T_blog set shareOtherCon=1 where id=@tmpresourceId
        end
      else if(@tmpresourceType='2')
         begin
           update T_user_link set shareOtherCon=1 where id=@tmpresourceId
         end
      else if(@tmpresourceType='3')
         begin
           update T_file_upload set shareOtherCon=1 where fileid=@tmpresourceId
         end


go



--select * from T_res_otherShareCondition
--
--select * from T_file_upload where fileid=6430
--update T_res_otherShareCondition set readshareto='',downshareto='' where id=33
--
--delete from T_res_otherShareCondition where id=31

-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
posted on 2009-04-29 13:42  钱途无梁  阅读(333)  评论(0)  编辑  收藏  举报