关于 sql触发器的 小问题

[SELECT1AND2]

table ColorDetail

ProdID LengthQty Dlvdate
A 10000 2017-8-25 ----------明细表（品名、数量、交期）



ProdID LengthQty Dlvdate
A 2000 2017-8-21 -----------产品A 分成了 2个数量 2个交期
A 8000 2017-8-25




在这个表里 添加触发器 向消息表里面 插入数据
如何插入 类似(产品A 原数量10000,交期2017-8-25 调整为数量2000,8000,交期2017-8-21 ,2017-8-25)的一条数据

我现在用 如下方法
select @NewLengthQty = ((stuff((select '，另数量为' +convert(varchar(30),LengthQty) from inserted where SheetSeries =
a.SheetSeries for xml path('')),1,2,'')) from inserted a group by SheetSeries ,ProdID )

但插入的数据就是两条.....有没有什么方法 求教??

[吉普赛的歌]

说的还是不明白， 你把触发器的完整代码， 造成了什么后果， 你想要什么样的结果再发一下吧

[SELECT1AND2]

ALTER  TRIGGER [insert_notice]
   ON  [OrderColor] 
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    DECLARE @Cnt1 INT,@Cnt2 INT,@Cnt3 INT
    DECLARE @SheetDate datetime,@SheetKind INT,@NoticeDate datetime,@Message VARCHAR(500),@Message2 VARCHAR(500),@RightStatus int,@Message4 VARCHAR(500),
            @NewLengthQty varchar(50),@OldLengthQty varchar(50),@NewDlvDate varchar(50),@OldDlvDate varchar(50),@Message3 VARCHAR(500),
    @Creater VARCHAR(50),@ProdID VARCHAR(50),
            @SheetNo varchar(50),@SheetSeries varchar(50)              
            
    --SELECT @SheetSeries=SheetSeries,@ProdID=ProdID,@NewLengthQty=LengthQty,@NewDlvDate=DlvDate FROM Inserted
    
    select @SheetSeries=SheetSeries from inserted group by SheetSeries
    
     select @ProdID=ProdID from inserted group by ProdID
        
      select  @NewLengthQty = (select (stuff((select '，另数量为' +convert(varchar(30),LengthQty) from inserted where SheetSeries =   
    a.SheetSeries for xml path('')),1,2,'')) from inserted  a group by SheetSeries ,ProdID)   
    
    select  @NewDlvDate =(select  (stuff((select '，另交期为' +convert(varchar(30),Dlvdate,11) from inserted  where SheetSeries =   
    a.SheetSeries for xml path('')),1,2,'')) from inserted  a group by SheetSeries ,ProdID ) 
    
    
    --SELECT @OldLengthQty=LengthQty,@OldDlvDate=DlvDate FROM deleted
    
      select  @OldLengthQty =  (select (stuff((select '，另数量为' +convert(varchar(30),LengthQty) from deleted where SheetSeries =   
    a.SheetSeries for xml path('')),1,2,'')) from deleted a group by SheetSeries ,ProdID  ) 
    
    select  @OldDlvDate = (select (stuff((select '，另交期为' +convert(varchar(30),Dlvdate,11) from deleted where SheetSeries =   
    a.SheetSeries for xml path('')),1,2,'')) from deleted a group by SheetSeries ,ProdID  )
    
    
    select @NoticeDate=GETDATE()
    SELECT @SheetNo=sheetno,@Creater=Creater,@SheetDate=SheetDate,@RightStatus=RightStatus,@SheetKind=SheetKind from OrderMain where SheetSeries=@SheetSeries
    SELECT @Message='大货订单改动提醒'
     SELECT @Message4='大货订单改动提醒(续)'
    
    
    SELECT @Message2='的品种，原'+@OldDlvDate+',调整为'+@NewDlvDate+'原'+@OldLengthQty+',调整为'+@NewLengthQty+'，请仔细核对'   
    
    --(CASE WHEN @NewDlvDate!=@OldDlvDate and @NewLengthQty!=@OldLengthQty then  @Creater+'提醒，'+'订单号为'+@SheetNo+'的单子有调整，'+@ProdID+'的品种，原交期为'+convert(varchar(30),@OldDlvDate,11)+'，原数量为'+convert(varchar(30),@OldLengthQty)+'，现调整交期为'+convert(varchar(30),@NewDlvDate,11)+'，数量为'+convert(varchar(30),@NewLengthQty)+'，请仔细核对' else
    --                  (case when @NewDlvDate!=@OldDlvDate and @NewLengthQty=@OldLengthQty  then  @Creater+'提醒，'+'订单号为'+@SheetNo+'的单子有调整，'+@ProdID+'的品种，原交期为'+convert(varchar(30),@OldDlvDate,11)+'，现调整交期为'+convert(varchar(30),@NewDlvDate,11)+'，数量仍为'+convert(varchar(30),@OldLengthQty)+'保持不变，请仔细核对' 
    --                   else @Creater+'提醒，'+'订单号为'+@SheetNo+'的单子有调整，'+@ProdID+'的品种，原数量为'+convert(varchar(30),@OldLengthQty)+'，现调整数量为'+convert(varchar(30),@NewLengthQty)+'，交期仍为'+convert(varchar(30),@OldDlvDate,11)+'，请仔细核对'  end)end)
                       

    SELECT @Message3= '另一个数量为：' +convert(varchar(30),@NewLengthQty)+'，交期为：'+convert(varchar(30),@NewDlvDate,11)+'，请仔细核对'                
    
                    
                       
    SELECT @Cnt1=COUNT(*) FROM Inserted
    SELECT @Cnt2=COUNT(*) FROM Deleted
    SELECT @Cnt3=COUNT(*) FROM OrderColor  where SheetSeries=@SheetSeries


   IF( @RightStatus=10 and @SheetKind=0)
    BEGIN
           INSERT INTO s_Notice(NoticeNo
    ,NoticeKind,NoticeWay,NoticeDate,IsSeen,Subject,Sender,Receiver
    ,SpecData,PriorityID,SheetNo,SheetSeries,FlowID,FlowTaskNo,StepNo,Content 
    ,SenderID,ReceiverID,BusinessUnitID,IsSend,IsSucceed,LinkInfoFrom,CustName
    ,NoticeName)
    VALUES  (  NEWID()
    ,1,4,@NoticeDate,0,@Message,@Creater,''
    ,NULL,NULL,@SheetNo,@SheetSeries,NULL,NULL,NULL,@Message2
    ,NULL,'',NULL,0,NULL,NULL, NULL,NULL
        )
        
        
               INSERT INTO s_Notice(NoticeNo
    ,NoticeKind,NoticeWay,NoticeDate,IsSeen,Subject,Sender,Receiver
    ,SpecData,PriorityID,SheetNo,SheetSeries,FlowID,FlowTaskNo,StepNo,Content 
    ,SenderID,ReceiverID,BusinessUnitID,IsSend,IsSucceed,LinkInfoFrom,CustName
    ,NoticeName)
    VALUES  (  NEWID()
    ,1,1,@NoticeDate,0,@Message,@Creater,''
    ,NULL,NULL,@SheetNo,@SheetSeries,NULL,NULL,NULL,@Message2
    ,NULL,'',NULL,0,NULL,NULL, NULL,NULL
        )
        

    END

END

[SELECT1AND2]

想要 这两条 并一条进行插入

[SELECT1AND2]

引用 1楼yenange 的回复:

说的还是不明白， 你把触发器的完整代码， 造成了什么后果， 你想要什么样的结果再发一下吧

发了 在楼下

[学海无涯-回头是岸]

看是插入的条件让它重复 还是在 inert into后面的 select 后面产生的子查询 重复。找到了 就可以解决了

[SELECT1AND2]

引用 5楼530303869 的回复:

看是插入的条件让它重复 还是在 inert into后面的 select 后面产生的子查询 重复。找到了 就可以解决了

是要把两条insert 合并成一条