合并语句和触发器SQL Server 2012

Question

我有一个合并语句应该多次执行触发器。

我首先想到我的触发器没有执行，但是通过一些研究，我发现触发器每个语句只触发一次（触发器是一个语句）。

但是所有的帖子都是旧的，我认为现在可能有一个简单的方法来让我的触发器执行多次。

那么有什么我可以添加到我的触发器或我的合并声明，使我的触发器这样做？

感谢

TRIGGER

TRIGGER [dbo].[Sofi_TERA_Trigger] 
    ON [dbo].[ZZ] 
    AFTER INSERT,UPDATE 
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements. 
    SET NOCOUNT ON; 

    IF EXISTS(SELECT 1 FROM inserted WHERE inserted.Statut LIKE '%CLOT%' OR inserted.Statut LIKE '%CLTT%' OR inserted.Statut LIKE '%CONF%') 
     BEGIN 
      DECLARE @Id int; 
      DECLARE @Matricule varchar(10); 
      DECLARE @IdAction int; 
      DECLARE @NumeroOF int; 
      SELECT @NumeroOF = inserted.Ordre from inserted; 

      DECLARE OF_CURSOR CURSOR 
       LOCAL STATIC READ_ONLY FORWARD_ONLY 
      FOR 
      SELECT Id,Log.Matricule,IdAction from Log inner join (select max(Id) as maxID,Matricule from LOG where Log.NumeroOF = @NumeroOF group by Matricule) maxID 
      on maxID.maxID = Log.Id where Log.NumeroOF = @NumeroOF; 

      OPEN OF_CURSOR 
      FETCH NEXT FROM OF_CURSOR INTO @Id,@Matricule,@IdAction 
      WHILE @@FETCH_STATUS = 0 
      BEGIN 
       IF @IdAction!=13 
        BEGIN 
         IF @IdAction<=2 
         BEGIN 
          insert into Log(NumeroOF,Matricule,IdAction,Date,EstAdmin) values (@NumeroOF,@Matricule,13,GETDATE(),1); 
         END 
         ELSE 
         BEGIN 
          insert into Log(NumeroOF,Matricule,IdAction,Date,EstAdmin) values (@NumeroOF,@Matricule,2,GETDATE(),1); 
          insert into Log(NumeroOF,Matricule,IdAction,Date,EstAdmin) values (@NumeroOF,@Matricule,13,GETDATE(),1); 
         END 
        END    
       FETCH NEXT FROM OF_CURSOR INTO @Id,@Matricule,@IdAction 
      END 

      CLOSE OF_CURSOR; 
      DEALLOCATE OF_CURSOR; 
    END 
END 

MERGE语句

Merge ZZ AS TARGET USING ZZTemp AS SOURCE 
ON (Target.Operation=Source.Operation AND Target.Ordre=Source.Ordre) 
WHEN MATCHED THEN 
UPDATE SET TARGET.DateTERA=SOURCE.DateTERA, TARGET.MatTERA=SOURCE.MatTERA, TARGET.MatTERC=SOURCE.MatTERC 
WHEN NOT MATCHED THEN      
INSERT(Operation,Ordre,ElementOTP,Article,DesignationOF,PosteTravail,ValeurTemps,DHT,Statut,StatutOF,TexteActivite,DateTERA,MatTERA,MatTERC,StatutMat)         
VALUES(SOURCE.Operation,SOURCE.Ordre,SOURCE.ElementOTP,SOURCE.Article,SOURCE.DesignationOF,SOURCE.PosteTravail,SOURCE.ValeurTemps,SOURCE.DHT, 
SOURCE.Statut,SOURCE.StatutOF,SOURCE.TexteActivite,SOURCE.DateTERA,SOURCE.MatTERA,SOURCE.MatTERC,SOURCE.StatutMat); 

Answer 1

您的问题是，光标不正确的写入处理的数据集。任何设置插入或删除标量变量的值形式的触发器都不正确，并且由于数据完整性的原因必须重写。这个触发器是越野车。期。没有必要重写它（以及其他使用相同技术的其他程序）。

您的触发器中的代码应该是这样的：

INSERT INTO Log(NumeroOF,Matricule,IdAction,Date,EstAdmin) 
SELECT max(Id),l.Matricule,l.IdAction, 13,GETDATE(),1 
FROM Log l 
JOIN Inserted i ON l.NumeroOF = i.Ordre 
WHERE i.Statut LIKE '%CLOT%' OR i.Statut LIKE '%CLTT%' OR i.Statut LIKE '%CONF%' 
GROUP BY l.Matricule,l.IdAction 

INSERT INTO Log(NumeroOF,Matricule,IdAction,Date,EstAdmin) 
SELECT max(Id),l.Matricule,l.IdAction, 2,GETDATE(),1 
FROM Log l 
JOIN Inserted i ON l.NumeroOF = i.Ordre 
WHERE IdAction<=2 
WHERE i.Statut LIKE '%CLOT%' OR i.Statut LIKE '%CLTT%' OR i.Statut LIKE '%CONF%' 
GROUP BY l.Matricule,l.IdAction 

确保与单记录和多个记录插入到测试所有触发器应该进行测试。一旦你确信触发器是正确的，然后尝试你的MERGE。