触发器,试着写了下,成功了,写进空间,学习下。
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
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