SqlServer 触发器在master/detail表中的一个应用

 

主表 MetroIncept 是一个委托单表，主要字段有：

 

• RecordId，委托单记录号，对应从表的 InceptRecId 字段。
• InceptFareSum， 金额，从表的金额合计值。
• InceptDetailCount， 委托器具数，从表的器具明细记录数。

 

从表 MetroInceptDetail 是委托单中的委托器具的明细表，主要字段有：

 

• InceptRecId， 委托单号，对应主表MetroIncept.RecordId。
• FareSum，收费金额

 

触发器功能要求：

• 当从表（委托明细表）记录改变时（新增、修改、删除），主表（委托单）的记录数字段和金额合计字段进行自动更新。

 

触发器程序：

 

-- MetroInceptDetail 新增后触发 
CREATE TRIGGER Incept_FareSum_insert  ON MetroInceptDetail AFTER INSERT AS
BEGIN
	DECLARE @FareSum_insert Money
	DECLARE @Counter_insert int
	DECLARE @InceptRecId 	int 

	SELECT @InceptRecId = MIN(InceptRecId) FROM Inserted

	while @InceptRecId is not null
	BEGIN
		-- 取得新增记录中本委托书号的记录数和金额合计 --
		SELECT 
                        @Counter_insert = COUNT(RecordId) , 
                        @FareSum_insert = SUM(ISNULL(FareSum, 0)) 
                     FROM Inserted 
                     WHERE InceptRecId = @InceptRecId

		-- 更新委托书 -- 
		update MetroIncept SET 
                            InceptFareSum = InceptFareSum + @FareSum_insert, 
                            InceptDetailCount = InceptDetailCount + @Counter_insert  
			WHERE MetroIncept.RecordId = @InceptRecId

		-- 取得下一个新增委托记录的委托单号 -- 
		SELECT @InceptRecId = MIN(InceptRecId) FROM Inserted 
                        WHERE InceptRecId > @InceptRecId
	END

END
go 


-- MetroInceptDetail 删除后触发 
CREATE TRIGGER Incept_FareSum_delete  ON MetroInceptDetail AFTER DELETE AS
BEGIN
	DECLARE @FareSum_delete Money
	DECLARE @Counter_delete int
	DECLARE @InceptRecId 	int 

	-- 取得最小的一个委托单
	SELECT @InceptRecId = MIN(InceptRecId) FROM deleted
	while @InceptRecId is not null
	BEGIN
		-- 取得删除记录中本委托书号的记录数和金额合计 --
		SELECT 
                      @Counter_delete = COUNT(RecordId) , 
                      @FareSum_delete = SUM(ISNULL(FareSum, 0)) 
                      FROM deleted 
                      WHERE InceptRecId = @InceptRecId
		-- 更新委托书 -- 
		UPDATE MetroIncept SET 
                             InceptFareSum = InceptFareSum - @FareSum_delete, 
                             InceptDetailCount = InceptDetailCount - @Counter_delete  
			WHERE MetroIncept.RecordId = @InceptRecId

		-- 取得下一个删除委托记录的委托单号 -- 
		SELECT @InceptRecId = MIN(InceptRecId) FROM deleted 
                         WHERE InceptRecId > @InceptRecId
	END

END
go 


-- MetroInceptDetail 更新后触发 
CREATE TRIGGER Incept_FareSum_update  ON MetroInceptDetail AFTER UPDATE AS
IF Update( FareSum )
BEGIN

	DECLARE @FareSum_delete Money, @FareSum_insert Money
	DECLARE @InceptRecId 	int 

	-- 取得最小的一个委托单
	SELECT @InceptRecId = MIN(InceptRecId) FROM deleted
	while @InceptRecId is not null
	BEGIN
		-- 取得删除记录中本委托书号的记录数和金额合计 --
		SELECT @FareSum_delete = SUM(ISNULL(FareSum, 0)) FROM deleted 
                 where InceptRecId = @InceptRecId

		-- 取得新增记录中本委托书号的记录数和金额合计 --
		SELECT @FareSum_insert = SUM(ISNULL(FareSum, 0)) FROM Inserted 
                where InceptRecId = @InceptRecId

		-- 更新委托书 -- 
		update MetroIncept set 
				InceptFareSum = InceptFareSum + @FareSum_insert - @FareSum_delete  
			WHERE MetroIncept.RecordId = @InceptRecId

		-- 取得下一个删除委托记录的委托单号 -- 
		SELECT @InceptRecId = MIN(InceptRecId) FROM deleted 
                where InceptRecId > @InceptRecId
	END


END
go