mssql 修改触发器_mssql 修改 trigger-优快云博客

该代码段定义了一个SQL触发器`processesDetail_update`，用于在`dbo.processesDetail`表中数据更新时处理产品完成数的更新。当`qtyFinish`字段被更新时，它会检查并更新产品的状态，同时根据工序名称更新`dbo.processes`表中的相应工序状态和单据状态。触发器确保只有在所有工序都完成时才更新单据的整体状态。

1.不讲解，直接上代码

USE [shkj]
GO
/****** Object:  Trigger [dbo].[processesDetail_update]    Script Date: 11/14/2021 22:52:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--更新产品完成数
ALTER TRIGGER [dbo].[processesDetail_update] ON [dbo].[processesDetail] FOR UPDATE 
AS
BEGIN
  IF NOT UPDATE(qtyFinish)  
    RETURN

  DECLARE @id INT,@processesName VARCHAR(15),@PID INT
  SELECT @id = Inserted.id,
         @PID = Inserted.PID,
		 @processesName = Inserted.processesName
   FROM Inserted  


   --更新产品状态
   UPDATE dbo.processesDetail SET status = 1 WHERE ISNULL(qtyFinish,0)>=ISNULL(qty,0)
   and id = @id
   
   --更新工序状态
  IF(NOT EXISTS(SELECT 1 FROM dbo.processesDetail WHERE PID = @PID AND status = 0 AND processesName=@processesName))
   BEGIN
	   UPDATE dbo.processes 
	   SET     jgStatus =  CASE when @processesName ='激光' THEN 1 ELSE jgStatus END,
			   zwStatus =  CASE when @processesName ='折弯' THEN 1 ELSE zwStatus END,
			   jjStatus =  CASE when @processesName ='机加' THEN 1 ELSE jjStatus END,
			   hjStatus =  CASE when @processesName ='焊接' THEN 1 ELSE hjStatus END,
			   qtStatus =  CASE when @processesName ='其他' THEN 1 ELSE qtStatus END
	   WHERE id = @PID
   END
 


   --更新单据状态
   IF(NOT EXISTS(SELECT 1 FROM dbo.processesDetail WHERE PID = @PID AND status = 0))
   BEGIN
	   UPDATE dbo.processes SET status = 1  
	   WHERE id = @PID 
   END
   
   
   

END