Create TRIGGER [dbo].[tr_Delete_AllocationedDN] --删除指定账户已分配未拣货的任务
ON [dbo].[FRU_PickLocationNew] --触发的表名
AFTER UPDATE --为什么触发
AS
IF UPDATE(IsUse) --哪个字段变更引发的触发?
BEGIN
IF ( ( SELECT IsUse
FROM inserted
) = 0 )
BEGIN
DELETE FROM WMS_FRU_DNAllocationToAccountNew
WHERE ID IN (
SELECT fdt.ID
FROM ( SELECT DISTINCT
Account
FROM dbo.FRU_PickLocationNew
WHERE IsUse = 0
) fpln
LEFT JOIN WMS_FRU_DNAllocationToAccountNew fdt ON fdt.Picker = fpln.Account
INNER JOIN dbo.WMS_Outbound wo ON wo.UserDefineAttribute01 = fdt.DN
AND WO.PickState <> 2
AND wo.ShipmentState <> 3
INNER JOIN dbo.WMS_OutboundPart wop ON wop.OutboundID = wo.OutboundID
INNER JOIN dbo.WMS_OutboundPick wopp ON wopp.OutboundPartID = wop.OutboundPartID
AND wopp.LocationCode = fdt.LocationCode
AND fdt.DN = wo.UserDefineAttribute01
AND fdt.PartNo = wopp.PartNO
WHERE ISNULL(wopp.PickBy, '') = ''
AND fpln.Account IN ( SELECT
Account
FROM
deleted ) )
END
END
表FRU_PickLocationNew(配置账户基础表)中有个字段IsUse(是否启用),一旦被更新成0(不启用)时,将表WMS_FRU_DNAllocationToAccountNew中对应的账户下所有已分配未完成的任务delete掉
触发器语句中使用了两种特殊的表:deleted 表和 inserted 表。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时,行从触发器表中删除,并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。
Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。
1.插入操作(Insert)
Inserted表有数据,Deleted表无数据
2.删除操作(Delete)
Inserted表无数据,Deleted表有数据
3.更新操作(Update)
Inserted表有数据(新数据),Deleted表有数据(旧数据)