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