ALTER TRIGGER [dbo].[Trigger_userStatusChangeLog]
ON [dbo].[Jinbi_User]
for UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewUserStatus int;
declare @OrgUserStatus int;
declare @UserID int;
declare @InWhiteList int;
select @UserID=ID, @NewUserStatus=userstatus ,@InWhiteList=isnull(InWhiteList,0) from inserted
select @OrgUserStatus=userstatus from Deleted
if(@OrgUserStatus=2 and @NewUserStatus=1 and @InWhiteList!=1)
begin
--ROLLBACK TRANSACTION
--update inserted set userstatus=@OrgUserStatus;
update dbo.[Jinbi_User] set userstatus=@OrgUserStatus where ID=@UserID;
end
else
begin
if(update(userstatus) and @NewUserStatus!=@OrgUserStatus )
begin
INSERT INTO [Jinbi].[dbo].[SYS_UserStatusChangeLog]
( [OrgUserStatus]
,[NewUserStatus]
,[UserID])
VALUES
( @OrgUserStatus,@NewUserStatus,@UserID)
end
end
END