如何做一个SQL Server触发器中的IF检查

问题描述：

我有我的桌子下面的Microsoft SQL Server触发器：如何做一个SQL Server触发器中的IF检查

-- Insert statements for trigger here 
DECLARE @AssignmentUID uniqueidentifier; 
DECLARE @ResourceUID uniqueidentifier; 
DECLARE @ResourceName nvarchar(255); 
DECLARE @ResourceClaimsAccount nvarchar(255); 
DECLARE @ProjectUID uniqueidentifier; 
DECLARE @ProjectName nvarchar(255); 
DECLARE @ProjectUrl nvarchar(1000); 
DECLARE @TaskUID uniqueidentifier; 
DECLARE @TaskName nvarchar(255); 
DECLARE @TaskUrl nvarchar(1000); 
DECLARE @UserId nvarchar(255); 
DECLARE @PWAUrl nvarchar(255); 
DECLARE @sql varchar(8000) 

SELECT @AssignmentUID = INSERTED.AssignmentUID 
FROM INSERTED; 

SELECT   
    @TaskUID = MSP_EpmAssignment_UserView.TaskUID, 
    @TaskName = MSP_EpmTask_UserView.TaskName, 
    @ProjectUID = MSP_EpmAssignment_UserView.ProjectUID, 
    @ProjectName = MSP_EpmProject_UserView.ProjectName, 
    @ProjectUrl = MSP_EpmProject_UserView.ProjectWorkspaceInternalHRef, 
    @ResourceUID = MSP_EpmAssignment_UserView.ResourceUID, 
    @ResourceClaimsAccount = MSP_EpmResource_UserView.UserClaimsAccount, 
    @ResourceName = MSP_EpmResource_UserView.ResourceName 
FROM 
    MSP_EpmAssignment_UserView 
LEFT OUTER JOIN 
    MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID 
LEFT OUTER JOIN 
    MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID 
LEFT OUTER JOIN 
    MSP_EpmTask_UserView ON MSP_EpmAssignment_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID 
WHERE 
    (MSP_EpmAssignment_UserView.AssignmentUID = @AssignmentUID); 

INSERT INTO tablename 
(ItemTitle, ParentName, ParentUrl, Loginname, SourceId, CreatedAt) 
VALUES (@TaskName, @ProjectName, @ProjectUrl, RIGHT(@ResourceClaimsAccount, CHARINDEX('\', REVERSE('\' + @ResourceClaimsAccount)) - 1), 1, GETDATE());

我如何检查是否@AssignmentUID和@TaskUID执行INSERT前IS NOT NULL INTO声明？是否还有可能优化此触发器？

你的触发器有** MAJOR **缺陷，你似乎认为它会被称为每行** ** - 这是**不是**的情况。触发器将在每个语句**中触发一次**，因此如果您的INSERT语句影响25行，您将触发**触发一次**，但是“插入”将分别包含25行。您的代码在这25行中选择哪一个？ 'SELECT @AssignmentUID = INSERTED.AssignmentUID FROM INSERTED' - 它是非确定性的。你需要重写你的触发器来考虑这个问题！ –

这是一个INSERT触发器。 INSERT触发器也可以这样吗？ – STORM

***是！***绝对！ *** SQL Server中的所有***触发器在每个语句中都有**一次，并处理可能的多行！（例如，如果您基于另一个表中的“SELECT”插入到表中） –

答

你需要写你的触发器在基于一套方式来处理该Inserted表可能包含多个行的事实。

试试这个代码 - 它使用你的核心SELECT，并且INNER JOIN到Inserted这个表上，你试图在开始时抓住这个表。这将一次处理全部行，包含在Inserted表中 - 无循环，无游标 - 不需要此类杂乱的代码！

INSERT INTO tablename (ItemTitle, ParentName, ParentUrl, Loginname, SourceId, CreatedAt) 
SELECT   
    MSP_EpmTask_UserView.TaskName, 
    MSP_EpmProject_UserView.ProjectName, 
    MSP_EpmProject_UserView.ProjectWorkspaceInternalHRef, 
    RIGHT(MSP_EpmResource_UserView.UserClaimsAccount, CHARINDEX('\', REVERSE('\' + MSP_EpmResource_UserView.UserClaimsAccount)) - 1, 
    1, 
    SYSDATETIME() 
FROM 
    MSP_EpmAssignment_UserView 
INNER JOIN 
    Inserted i ON MSP_EpmAssignment_UserView.AssignmentUID = i.AssignmentUID 
LEFT OUTER JOIN 
    MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID 
LEFT OUTER JOIN 
    MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID 
LEFT OUTER JOIN 
    MSP_EpmTask_UserView ON MSP_EpmAssignment_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID

答

IF (@AssignmentUID IS NOT NULL AND @TaskUID IS NOT NULL) 
BEGIN 
    ... 
END

如何做一个SQL Server触发器中的IF检查

相关推荐