Count(*) Not Working Properly

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 tr

Solution 1:

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.

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.

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.

The following trigger works for me:

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;

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.

Solution 2:

You need to do an AFTER trigger, not a BEFORE trigger. Doing a count(*) "BEFORE" the insert occurs results in zero rows because the data hasn't been inserted yet.