触发器定义内的DELETE语句问题 [英] DELETE statement issues within a trigger definition

问题描述

我创建了一个插入/更新触发器，该触发器旨在根据要插入的数据来更新其他表中的信息。触发器要做（或应该做）的最后一件事是从目标表中删除所有数据，条件是在触发器的插入部分期间可能已更改了条件。

I have created an insert/update trigger that is designed to update information in a different table based on the data being inserted. The last thing the trigger does (or is supposed to do) is remove all data from a target table with conditions that may have changed during the insert portions of the trigger.

除了最后的 DELETE 语句外，其他所有内容似乎都与触发器一起工作。它正在执行 DELETE 语句，但不遵循where子句中的任何条件。它只是删除表中的所有内容。

Everything appears to be working with the trigger except the final DELETE statement. It is executing the DELETE statement, but not following any of the conditions in the where clause. It simply deletes everything in the table.

我什至尝试将where子句中的 NOT IN 更改为 IN ，它仍然执行相同操作。我隔离了 DELETE 语句并在触发器之外进行了测试，它可以正常工作（使用相同的变量和子查询）。

I have even tried changing the NOT IN in the where clause to IN, and it still does the same. I have isolated the DELETE statement and tested outside the trigger and it works fine (using the same variables and subqueries).

我是否缺少触发器行为？

Am I missing something with the behavior of a trigger?

代码如下：

ALTER TRIGGER [dbo].[cust_trgr_profile_attribute] 
ON [dbo].[port_module_instance_setting]      
AFTER INSERT, UPDATE
AS

DECLARE @ModuleId INT=449,
        @MatchGroupModSetting VARCHAR(50) = 'AttributeGroup',
        @FilterGroupModSetting VARCHAR(50) = 'FilterAttributeGroup',
        @MatchAttributes TABLE (attribute_id INT),
        @FilterAttributes TABLE (attribute_id INT)

INSERT INTO @MatchAttributes
  SELECT DISTINCT camatch.attribute_id 
  FROM inserted I
  JOIN core_attribute camatch ON I.value = CONVERT(VARCHAR(10), camatch.attribute_group_id)
  JOIN port_module_instance pmi ON I.module_instance_id = pmi.module_instance_id
       AND pmi.module_id=@ModuleId
  WHERE I.name like @MatchGroupModSetting+'_'

INSERT INTO @FilterAttributes
  SELECT DISTINCT cafilter.attribute_id 
  FROM inserted I
  JOIN core_attribute cafilter ON I.value = CONVERT(VARCHAR(10), cafilter.attribute_group_id)
  JOIN port_module_instance pmi ON I.module_instance_id = pmi.module_instance_id
       AND pmi.module_id=@ModuleId
  WHERE I.name=@FilterGroupModSetting

IF ((SELECT COUNT(*) FROM @MatchAttributes) > 0 OR (SELECT COUNT(*) FROM @FilterAttributes) > 0)
BEGIN
    IF (SELECT COUNT(*) FROM @MatchAttributes) > 0
    BEGIN
        UPDATE cpa 
           SET cpa.[required]=0
          FROM cust_profile_attribute cpa
          JOIN @MatchAttributes ma ON cpa.attribute_id = ma.attribute_id
    END

    IF (SELECT COUNT(*) FROM @FilterAttributes) > 0
    BEGIN
        UPDATE cpa 
           SET cpa.[required]=0
          FROM cust_profile_attribute cpa
          JOIN @FilterAttributes fa ON cpa.attribute_id=fa.attribute_id
    END

    DELETE FROM cust_profile_attribute
    WHERE attribute_id NOT IN (SELECT distinct ca.attribute_id 
                               FROM core_attribute ca
                               JOIN port_module_instance_setting inst ON CONVERT(VARCHAR(10),ca.attribute_group_id) = inst.value
                               JOIN port_module_instance modinst ON inst.module_instance_id = modinst.module_instance_id
                                    AND modinst.module_id = @ModuleId
                               WHERE inst.name like @MatchGroupModSetting + '_'
                               OR inst.name like @FilterGroupModSetting)
END

推荐答案

我发现我的基本面有缺陷触发器的工作原理。现在，我对所发生的事情有了更深入的了解，并能够阐明更多信息，以帮助他人。与其尝试完全改变原始帖子，不如将新信息发布在这里。

I have found a flaw in my fundamental logic of how the trigger works. I have now refined my understanding of what is going on and able to articulate more information for others to help. Rather than try to completely transform the original post, I thought it better to post the new info here.

基本思想是 port_module_instance_setting 表中的表示设置的字符串（在此特定情况下，条件已设置，以使 value 字段始终为数字） 。我要完成的工作是，当在这些特定设置之一中更新 value 字段时， cust_profile_attribute 值引用的$ c>表将被删除。在这种情况下， port_module_instance_setting 的 value 字段是一个数字varchar值，直接引用 core_attribute 的attribute_group_id 。请不要评论有关使用不同数据类型的表的最佳实践，因为我无法控制实际的表结构：)
触发器中的所有内容都可以正常运行，但 DELETE除外语句结尾。它什么也没做。关于为什么不删除我想要的属性的任何想法？

The basic idea is that the port_module_instance_setting table stores a string in the that represents a setting (in this specific case, the conditions are set so that the value field will always be a number). What I am trying to accomplish is that when the value field is updated in one of these specific "settings", everything in the cust_profile_attribute table that is referenced by the old value gets deleted. In this context, the value field of port_module_instance_setting is a numeric varchar value that directly references the attribute_group_id of core_attribute. Please don't comment on best practices regarding referencing tables using different data types, as I have no control over the actual table structure :) Everything in the trigger functions properly, except the DELETE statement at the end. It isn't doing anything. Any ideas on why it isn't deleting the attributes I want it to?

我更改了 DELETE 语句如下所示，以引用预先更新的值字段。

I have changed the DELETE statement as follows to reference the pre-updated valuefield.

DELETE FROM cust_profile_attribute
WHERE attribute_id IN(SELECT ISNULL(attribute_id,-1) FROM deleted d
                      JOIN core_attribute ca ON ca.attribute_group_id= CONVERT(INT,d.value))

这篇关于触发器定义内的DELETE语句问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！