销售单据关闭状态触发器 SQLserver

玫城

已于 2023-01-07 14:13:34 修改

阅读量178

点赞数

分类专栏：数据库文章标签： sqlserver 数据库

于 2023-01-07 09:37:00 首次发布

本文链接：https://blog.csdn.net/weixin_70506521/article/details/128587770

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

USE [AIS20221128142101]
GO
/****** Object:  Trigger [dbo].[wsd_install_status_update]    Script Date: 2023-01-07 14:11:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--安装费关闭状态触发器
/*
 查询单据体安装费关闭状态(字段:FInteger1),若状态为1(关闭状态),
	再查询单据体调试费关闭状态(字段:FDecimal4),若此状态为0,修改安装费田试状态为0
*/

ALTER TRIGGER [dbo].[wsd_install_status_update] ON [dbo].[wsd_hkdjtz_mx]
FOR UPDATE
AS 
BEGIN
	SET NOCOUNT ON
	if update(FInteger1) or update(FDecimal4)  --监控两个字段
	begin	
		declare @FID int
		declare @FEntryID int
		declare @FInteger1 int --安装费关闭状态
		declare @FDecimal4 int --调试费关闭状态
		declare @FAmount6 decimal(20,6) --应付安装费
		declare @FAmount4 decimal(20,6) --已付安装费
		declare cur cursor for select FID, FEntryID, FInteger1, FDecimal4,FAmount6,FAmount4  from Inserted
		open cur
		fetch next from cur into @FID, @FEntryID, @FInteger1, @FDecimal4, @FAmount6, @FAmount4

		while @@FETCH_STATUS = 0
		begin
			if @FInteger1 = 1 --监控安装费关闭状态:安装费关闭状态计划变为1时,判断调试费关闭状态,若为0,将安装费关闭状态计划变为 0
			begin
				if 0 = ( select FDecimal4 from wsd_hkdjtz_mx where FEntryID = @FEntryID and FID = @FID )
				begin
					update wsd_hkdjtz_mx set FInteger1 = 0 where FEntryID = @FEntryID and FID = @FID
				end
			end
			if @FDecimal4 = 1 --监控调试费关闭状态:调试费关闭状态计划变为1时,判断(应付安装费-已付安装费)是否为0,若为零,将安装费关闭状态计划变为 1
			begin
				if 0 = ( select (FAmount6 - FAmount4 ) from wsd_hkdjtz_mx where FEntryID = @FEntryID and FID = @FID )
				begin
					update wsd_hkdjtz_mx set FInteger1 = 1 where FEntryID = @FEntryID and FID = @FID
				end
			end
			fetch next from cur into @FID, @FEntryID, @FInteger1, @FDecimal4, @FAmount6, @FAmount4
		end
		close cur
		deallocate cur
	end
END