计数（*）无法正常工作 [英] Count(*) not working properly

问题描述

我创建触发器A1，以便具有某种类型的文章（即Bert）不能多次添加，并且在库存中只能有1个。
然而，虽然我创建触发器，我仍然可以添加一个类型为'Bert'的文章。不知何故，计数返回0，但是当我运行相同的sql语句，它返回正确的数字。它也开始计数正确，如果我删除触发器并重新添加。任何想法可能会出错？

I create the trigger A1 so that an article with a certain type, that is 'Bert' cannot be added more than once and it can have only 1 in the stock. However, although i create the trigger, i can still add an article with the type 'Bert'. Somehow, the count returns '0' but when i run the same sql statement, it returns the correct number. It also starts counting properly if I drop the trigger and re-add it. Any ideas what might be going wrong?

TRIGGER A1 BEFORE INSERT ON mytable
FOR EACH ROW
DECLARE
 l_count NUMBER;
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  SELECT COUNT(*) INTO l_count FROM mytable WHERE article = :new.article;

    dbms_output.put_line('Count: ' || l_count);
  IF l_count >0  THEN
    IF(:new.TYPEB = 'Bert') THEN 
      dbms_output.put_line('article already exists!');
      ROLLBACK; 
    END IF;
  ELSIF (:new.TYPEB = 'Bert' AND :new.stock_count>1) THEN
    dbms_output.put_line('stock cannot have more than 1 of this article with type Bert');
    ROLLBACK; 
  END IF;
END;

这是我使用的insert语句：

This is the insert statement I use:

INSERT INTO mytable VALUES('Chip',1,9,1,'Bert');

推荐答案

几点。首先，你滥用自主的事务pragma。它用于单独的事务，您需要独立于主事务提交或回滚。您正在使用它来回滚主要事务 - 如果没有错误，您从不提交。

A couple of points. First, you are misusing the autonomous transaction pragma. It is meant for separate transactions you need to commit or rollback independently of the main transaction. You are using it to rollback the main transaction -- and you never commit if there is no error.

有人提到的那些不可预见的后果其中一个是你的计数总是返回0.所以删除pragma，因为它被滥用，所以计数将返回一个合适的值。

And those "unforeseen consequences" someone mentioned? One of them is that your count always returns 0. So remove the pragma both because it is being misused and so the count will return a proper value.

另一件事是don在触发器中没有提交或回滚。引发一个错误，让控制代码做它需要做的。我知道回滚是因为pragma伪指令。

Another thing is don't have commits or rollbacks within triggers. Raise an error and let the controlling code do what it needs to do. I know the rollbacks were because of the pragma. Just don't forget to remove them when you remove the pragma.

以下触发器适用于我：

CREATE OR REPLACE TRIGGER trg_mytable_biu 
BEFORE INSERT OR UPDATE ON mytable 
FOR EACH ROW 
WHEN (NEW.TYPEB = 'Bert') -- Don't even execute unless this is Bert
DECLARE
    L_COUNT NUMBER;
BEGIN
    SELECT  COUNT(*) INTO L_COUNT
    FROM    MYTABLE 
    WHERE   ARTICLE = :NEW.ARTICLE
        AND TYPEB = :NEW.TYPEB;

    IF L_COUNT > 0  THEN
        RAISE_APPLICATION_ERROR( -20001, 'Bert already exists!' );
    ELSIF :NEW.STOCK_COUNT > 1 THEN
        RAISE_APPLICATION_ERROR( -20001, 'Can''t insert more than one Bert!' );
    END IF;
END;

但是，对表上的触发器单独访问该表并不是一个好主意。通常，系统甚至不会允许它 - 如果更改为after，此触发器将根本不会执行。如果允许执行，就不能确定获得的结果 - 正如你已经发现的。其实，我有点惊讶的触发器上面的作品。

However, it's not a good idea for a trigger on a table to separately access that table. Usually the system won't even allow it -- this trigger won't execute at all if changed to "after". If it is allowed to execute, one can never be sure of the results obtained -- as you already found out. Actually, I'm a little surprised the trigger above works. I would feel uneasy using it in a real database.

当触发器必须访问目标表时，最好的选择是隐藏表格一个视图并在视图上写入一个而不是触发器。 触发器可以访问所有想要的表。

The best option when a trigger must access the target table is to hide the table behind a view and write an "instead of" trigger on the view. That trigger can access the table all it wants.

这篇关于计数（*）无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！