时间触发器

最新推荐文章于 2023-09-14 16:00:10 发布

原创最新推荐文章于 2023-09-14 16:00:10 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#triggers #nokia #insert #null #存储

Sql 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一个SQL触发器的设计方案，该触发器在出港分单表和DN与分单关系表中实现。当分单表中的PickupTime更新且与DN存在关联时，会向edi_TimeAlerts表中添加记录。此外，还提供了一个存储过程用于插入新的时间警报记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在原先出港分单（dom_Ex_HAWBInfo）表中的触发器基础上，在DN与分单关系表（Nokia_Relation_DnAndHawb）中也创建触发器。出港分单上的触发器上触发机制不变（在Pickuptime中新增时往edi_TimeAlerts写入一条记录）。DN与分单关系表中的触发器上触发机制为当表中有新增或者修改的记录时，如果分单表中的时间不为空时则往edi_TimeAlerts插入一条记录。
还有一个地方要修改，是生成报文的服务中去判断一下需要生成报文记录的“分单时间和对应的DN”不能为空。

如果出口分单表中的PickupTime时间填写时则触发

/* Name: edi_ExportHawbPickup_Changing Type: Trigger Description: 当PickupTime的时间发生改变时，向状态变化表插入一条记录 Add by fubin.pan */ --(@OldPickupTime IS NOT NULL AND @OldPickupTime <> @NewPickupTime) OR CREATE TRIGGER [edi_ExportHawbPickup_Changing] ON [dbo].[dom_Ex_HAWBInfo] AFTER INSERT, UPDATE AS BEGIN IF (UPDATE(PickupTime)) BEGIN DECLARE @OldPickupTime datetime DECLARE @NewPickupTime datetime DECLARE @Hawbno varchar(20) DECLARE @Cmp_id varchar(10) SELECT @OldPickupTime = PickupTime FROM deleted SELECT @NewPickupTime = PickupTime, @Hawbno = hawbno, @Cmp_id = CustomerNO FROM inserted IF ((@OldPickupTime IS NULL AND @NewPickupTime IS NOT NULL) AND (EXISTS (SELECT 1 FROM Nokia_Relation_DnAndHawb WHERE Hawb = @Hawbno))) BEGIN EXECUTE edi_TimeAlerts_Append @Cmp_id, 'PICKUP', @Hawbno END END END

生成一条记录的存储过程

/* Name: edi_TimeAlerts_Append Type: Procedure Description: 向edi_TimeAlerts表中添加一条记录 */ CREATE PROCEDURE [dbo].[edi_TimeAlerts_Append] @Cmp_id varchar(10), @TimeTypeName varchar(50), @Hawbno varchar(10) AS BEGIN DECLARE @TriggerId uniqueidentifier DECLARE @CustomerId uniqueidentifier DECLARE @TimeTypeId uniqueidentifier DECLARE @ExportHawbId uniqueidentifier SELECT @CustomerId = Gid FROM dom_CustomerIDControl WHERE Cmp_id = @Cmp_id SELECT @TimeTypeId = TimeTypeId FROM edi_TimeAlertTypes WHERE TimeTypeName = @TimeTypeName IF (@TimeTypeName = 'POD') BEGIN SELECT @ExportHawbId = GID FROM dom_ImportHawb WHERE IMPH_HAWBID = @Hawbno END ELSE IF (@TimeTypeName = 'PICKUP') BEGIN SELECT @ExportHawbId = GID FROM dom_Ex_HAWBInfo WHERE HAWBNO = @Hawbno END SELECT @TriggerId = TriggerId FROM edi_Triggers WHERE TimeTypeId = @TimeTypeId AND CustomerId = @CustomerId IF EXISTS ( SELECT 1 FROM edi_Triggers WHERE TimeTypeId = @TimeTypeId AND CustomerId = @CustomerId) BEGIN INSERT INTO edi_TimeAlerts(TriggerId, HAWBId, HawbNo, AlertTime) VALUES(@TriggerId, @ExportHawbId, @HawbNo, getdate()) print 'inserting time alert which type is ' + @TimeTypeName + ' and hawbno is ' + @Hawbno END END