sybase触发器

触发器，试着写了下，成功了，写进空间，学习下。

1. 插入触发器
#TB_USER上的触发器，每注册一个用户，TB_COUNTUSER表中该机构的注册和申请待审核状态统计都加1
create   trigger   insert_TB_USER
  on TB_USER for insert
  as    
  declare   @raid   int  , @userCount int , @registerReviewCount int
begin
  select @raid = RA_ID  from inserted
     select @userCount = USER_COUNT , @userRegisterToReviewCount = USER_REGISTER_TO_REVIEW_COUNT from TB_COUNTUSER where RA_ID = @raid

  if(@userCount = null)   --1. 注册用户
         update TB_COUNTUSER set USER_COUNT = 1 where RA_ID = @raid
  else
   update TB_COUNTUSER set USER_COUNT = @userCount + 1  where RA_ID = @raid  

if(@userRegisterToReviewCount = null)
   update TB_COUNTUSER set  USER_REGISTER_TO_REVIEW_COUNT = 1 where RA_ID = @raid
else
  update TB_COUNTUSER set  USER_REGISTER_TO_REVIEW_COUNT = @userRegisterToReviewCount + 1 where RA_ID = @raid  
end


2. 修改触发器
create   trigger   checkPast_TB_USER
  on TB_USER for update
  as    
  --查询需要的参数
  declare   @userStatus int , @beginUserStatus int , @raid int
  --综合的四个参数
  declare   @userCount int , @userIssuedCount int , @userFreezedCount int , @userCertRevokedCount int
  --九个参数(延期待审核在本项目中没用到)
  declare   @userRegisterToReviewCount int , @userUpdateToReviewCount int , @userRecoverToReviewCount int , @userReapplyToReviewCount int
  declare   @userFreezeToReviewCount int , @userUnFreezeToReviewCount int , @userPostoneToReviewCount int , @userKeyUpdateToReviewCount int
  declare   @userRevokedToReviewCount int
  --九个参数(待延期在本项目中没用到)
  declare   @userToIssueCount int , @userToUpdateCount int , @userToRecoverCount  int , @userToReissueCount  int  
  declare   @userToFreezeCount int , @userToUnFreezeCount int , @userToPostoneCount  int , @userToKeyUpdateCount  int  
  declare   @userToRevokedCount int  
  
                                
  select @userStatus = USER_STATUS_ID from inserted      --修改之后的USER_STATUS_ID状态值
  select @beginUserStatus = USER_STATUS_ID from deleted     --修改之前的USER_STATUS_ID状态值
  select @raid = RA_ID  from inserted       --修改动作所操作的RA_ID机构号

if (@userStatus = 1002 and @beginUserStatus = 1000)   --2.从已注册审核通过
begin
  select @userToIssueCount= USER_TO_ISSUE_COUNT , @userRegisterToReviewCount = USER_REGISTER_TO_REVIEW_COUNT from TB_COUNTUSER where RA_ID = @raid
if(@userToIssueCount= null)
  update TB_COUNTUSER set USER_TO_ISSUE_COUNT = 1 where RA_ID = @raid
else
  update TB_COUNTUSER set USER_TO_ISSUE_COUNT = @userToIssueCount + 1  where RA_ID = @raid  
  update TB_COUNTUSER set  USER_REGISTER_TO_REVIEW_COUNT = @userRegisterToReviewCount - 1 where RA_ID = @raid  
end


3。删除触发器
#TB_USER上的触发器，删除触发器(注销用户时使用)
create   trigger   delete_TB_USER
  on TB_USER for delete
  as    
  declare   @beginUserStatus  int
  declare   @raid   int  , @registerReviewCount int , @userCount int  
  select @beginUserStatus = USER_STATUS_ID from deleted     --修改之前的状态值
if (@beginUserStatus = -1)    --从已注册状态注销用户
begin
  select @raid = RA_ID  from deleted
  select @userCount = USER_COUNT  from TB_COUNTUSER where RA_ID = @raid  
  update TB_COUNTUSER set USER_COUNT = @userCount - 1  where RA_ID = @raid  
end