sql中执行语句:

/****** Object: Trigger [dbo].[trg_t_User_InsUptDel] Script Date: 2020/5/11 14:29:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[trg_t_User_InsUptDel] ON [dbo].[t_User]

INSTEAD OF INSERT,UPDATE,DELETE

AS

BEGIN

BEGIN TRANSACTION

SET NOCOUNT ON

DECLARE @ERROR INT

SET @ERROR=0

IF EXISTS(SELECT 1 FROM Deleted)

BEGIN

DELETE t_Base_User FROM t_Base_User INNER JOIN Deleted ON t_Base_User.FUserID=Deleted.FUserID

SET @ERROR=@ERROR+@@ERROR

DELETE HR_Base_User FROM HR_Base_User INNER JOIN (SELECT FUserID FROM Deleted WHERE NOT EXISTS(SELECT FUserID FROM Inserted WHERE Deleted.FUserID=Inserted.FUserID)) t1 ON HR_Base_User.FUserID=t1.FUserID

SET @ERROR=@ERROR+@@ERROR

END

IF EXISTS (SELECT 1 FROM Inserted)

BEGIN

INSERT INTO t_Base_User (FUserID,FName,FSID,FPrimaryGroup,FPortUser,FDescription,FForbidden,FRight,FEmpID,FDataVokeType,FIsNeedOffline,FRightChanged,FOfflineRefeshEachTime,

FSafeMode,FHRUser,FSSOUsername,FSCPwd,UUID,FAccessUUID,FUInValidDate,FPwCreateDate,FPwValidDay,FAuthRight,FUserTypeID,FSupplierID,FCustomerID,FIMUserName,FSupUnit)

SELECT FUserID,FName,FSID,FPrimaryGroup,ISNULL(FPortUser,''),FDescription,ISNULL(FForbidden, 0),ISNULL(FRight,0x00),ISNULL(FEmpID,0),ISNULL(FDataVokeType,0),ISNULL(FIsNeedOffline,0),ISNULL(FRightChanged,0),ISNULL(FOfflineRefeshEachTime,0),

ISNULL(FSafeMode,0),ISNULL(FHRUser,0),FSSOUsername,FSCPwd,ISNULL(UUID,NEWID()),ISNULL(FAccessUUID,0),ISNULL(FUInValidDate,0),ISNULL(FPwCreateDate,0),ISNULL(FPwValidDay,0),ISNULL(FAuthRight,4),ISNULL(FUserTypeID,0),ISNULL(FSupplierID,''),ISNULL(FCustomerID,0),ISNULL(FIMUserName,'') ,ISNULL(FSupUnit,'') FROM Inserted

SET @ERROR=@ERROR+@@ERROR

UPDATE HR_Base_User SET Account=Inserted.FName,Description=Inserted.FDescription,IsStop=Inserted.FForbidden,PasswordHashValue=Inserted.PasswordHashValue,

FSafeMode=ISNULL(Inserted.FSafeMode,0) FROM HR_Base_User,Inserted WHERE HR_Base_User.FUserID=Inserted.FUserID AND Inserted.FUserID IN (SELECT Inserted.FUserID FROM Deleted,Inserted WHERE Deleted.FUserID=Inserted.FUserID)

SET @ERROR=@ERROR+@@ERROR

INSERT INTO HR_Base_User(ID,Account,Description,IsStop,FUserID,PasswordHashValue,FSafeMode)

SELECT [ID],FName,FDescription,FForbidden,FUserID,PasswordHashValue,ISNULL(FSafeMode,0) FROM Inserted

WHERE FUserID NOT IN (SELECT Inserted.FUserID FROM Deleted,Inserted WHERE Deleted.FUserID=Inserted.FUserID)

IF UPDATE(FSID)

UPDATE t1 SET t1.PasswordEditDate=GETDATE() FROM HR_Base_User t1 INNER JOIN inserted t2 ON t1.FUserID=t2.FUserID

SET @ERROR=@ERROR+@@ERROR

END

IF (@ERROR <> 0)

ROLLBACK TRANSACTION

ELSE

COMMIT TRANSACTION

END

GO