主表 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