删除后的触发器在更改后不起作用

问题描述：

我在使用Microsoft SQL Server Management Studio的T-SQL中编写了一个触发器，当我发现一个缺陷时，我缩小了触发器的范围。这使我的一个触发器停止工作，我不知道为什么。删除后的触发器在更改后不起作用

工作流应该是这样的：Delete Event，通过EventId上的外键，EventEquipment被删除。由于不再需要设备，因此应将设备号码添加回名为设备可用性的表中设备的可用性。

以下是我的代码。我对插入工作减少可用性和直到我加入到只对日期

create trigger trgAddNumberAvailable 
on EventEquipments 
after delete 
as 
begin 

    declare @eventEquipmentId int; 

    select @eventEquipmentId = EventEquipmentId 
    from deleted; 

    declare @eventId int; 

    select @eventId = EventId 
    from deleted; 

    --selects the eventID from EventEquipment 

    declare @eventDate date; 

    select @eventDate = EventDate 
    from Events 
    where Events.EventId = @eventId; 
    --finds the date for the Event that was chosen above becuase EventEquipment does not contain date 

    declare @equipmentId int; 

    select @equipmentId = EquipmentId 
    from deleted 
    where deleted.EventEquipmentId = @eventEquipmentId; 

    declare @numberRequested int; 

    select @numberRequested = NumberOfEquipmentNeeded 
    from deleted 
    where deleted.EventEquipmentId = @eventEquipmentId; 

    update EquipmentAvailabilities 
    set NumberAvailable = NumberAvailable + @numberRequested 
    where EquipmentId = @equipmentId 
     and EquipmentAvailabilityDate = @eventDate; 

    --will update the availability for the equipment from insert and the date it is 
    --available that was chosen from the Event date from the event that was deleted

你的触发器有一个** MAJOR **缺陷，你似乎认为它会被称为**每行**一次 - 事实并非如此。触发器会在每个语句**中触发一次**，所以如果你的DELETE语句影响了25行，你将触发**触发一次**，但是那么'Deleted'伪表将包含25行。您的代码在这25行中选择哪一个？ 'select @eventEquipmentId = EventEquipmentId from deleted;' - 这是非确定性的，你会得到**一个任意的行**，你将**忽略所有其他行**。你需要重写你的触发器来考虑这个问题！ –

删除语句一次只影响一行 – throwaway3834

它可能会删除一行，但您应该为批量删除做好准备。另外新的开发人员不知道所有这些东西，所以你应该在重要的商业规则中额外的谨慎。当你使用这么多的id时，那么eventdate的需求是什么？在表格关系中看起来似乎不存在任何东西。你可以复制粘贴你在SSMS中的触发器脚本，而不是删除把真实的表名。这种方式，你可以调试它。 – KumarHarsh

答

你HV使用了这么多次选择..从删除只是为了填充变量做后将其删除工作过这种附加的可用性。改为使用一个选择并填充所有变量。

另请注意替代查询。表之间的关系不清楚。

create trigger trgAddNumberAvailable 
on EventEquipments 
after delete 
as 
begin 

    declare @eventEquipmentId int; 
    declare @eventId int; 
    declare @eventDate date; 
    declare @equipmentId int; 
    declare @numberRequested int; 

    select @eventEquipmentId = EventEquipmentId, @eventId = EventId 
    ,@equipmentId = EquipmentId,@numberRequested = NumberOfEquipmentNeeded 
    from deleted; 

    --selects the eventID from EventEquipment 
     select @eventDate = EventDate 
    from Events 
    where Events.EventId = @eventId; 
    --finds the date for the Event that was chosen above becuase EventEquipment does not contain date 

    update EquipmentAvailabilities 
    set NumberAvailable = NumberAvailable + @numberRequested 
    where EquipmentId = @equipmentId 
     and EquipmentAvailabilityDate = @eventDate; 

    --will update the availability for the equipment from insert and the date it is 
    --available that was chosen from the Event date from the event that was deleted 

    --OR (alternative query for bulk delete ,(Correct the join)) 
    update EquipmentAvailabilities 
    set NumberAvailable = NumberAvailable + @numberRequested 
    from EquipmentAvailabilities EA 
    inner join deleted d 
    on ea.EquipmentId = d.equipmentId 
    inner join Events E 
    on d.EventId=e.EventId 
    where 
     ea.EquipmentAvailabilityDate = e.eventDate;

删除后的触发器在更改后不起作用

相关推荐