Using before insert trigger with select .. for update

Using before insert trigger with select .. for update

Problem Description:

I have a table with the following values:

User 1 has permissions 2 and 4,
user 2 has permissions 4 and
user 3 has all permissions (null).

The rule is, if you want to insert null permission for user 1, it needs to fail, unless there are
no records of that user in the table.

I tried doing the following

create or replace trigger my_trigger
  before insert or update
  on my_table
  for each row
declare
  w_count number;
PRAGMA AUTONOMOUS_TRANSACTION;  
begin
  if :new.permission is null then
    select count(*)
      into w_count
      from my_table
     where user_id  = :new.user_id  
       and permission is not null;  

    if w_count > 1 then
      --raise error, cannot insert user with null permission, because records with numbered permissions exist. Delete the existing records first

    end if;

  else
    select count(*)
      into w_count
      from my_table
     where user_id  = :new.user_id  
       and permission   is null;
       
    if w_count > 1 then
      --raise error, cannot insert user with numbered permissions, because records with null permission exists. Delete the existing records first.
    end if;
  end if;
end;

This works if I insert or update rows one at a time. But if I use SELECT * FROM my_table FOR UPDATE, delete one row of user 1 and edit the other row to permission=null, then the behaviour is not correct, because count function still sees the old values, even if the database gui shows correct ones. What could I do to prevent this behaviour?

Solution – 1

Ideally, one would have these rules in the front-end application code. If that is not possible, it might be best to use a compound trigger. This type of trigger will eliminate the need for the PRAGMA AUTONOMOUS_TRANSACTION statement. It will also allow the table that fired the trigger to be queried without causing a mutating table error (ORA-04091). Below are two links that explain how compound triggers work.

• Stack Overflow: Compound
  Triggers

• Ask Tom: Compound
  Triggers

A compound trigger will allow one to:

1. Gather the inserts/updates into a collection – within the BEFORE EACH ROW section of the trigger.
2. Query the table that fired the trigger – within the AFTER STATEMENT section of the trigger.
3. Prevent the transaction from completing if any part of it violates one of the business rules.

CREATE OR REPLACE TRIGGER my_trigger FOR
    INSERT OR UPDATE ON my_table
COMPOUND TRIGGER

    TYPE my_tbl_t IS TABLE OF my_table%rowtype INDEX BY PLS_INTEGER;
    l_my_tbl my_tbl_t;

    BEFORE EACH ROW
    IS
        l_count PLS_INTEGER;
    BEGIN
        l_count := l_my_tbl.count + 1;
    -- save all records to PLSQL table
        l_my_tbl(l_count).user_id := :new.user_id;
        l_my_tbl(l_count).permission := :new.permission;
    END BEFORE EACH ROW;

    AFTER STATEMENT
    IS
        l_count PLS_INTEGER;
    BEGIN
    
    -- loop through all records in collection
        FOR i IN 1..l_my_tbl.count LOOP
        
            IF ( l_my_tbl(i).permission IS NULL ) THEN
            
                SELECT COUNT(*)
                  INTO l_count
                  FROM my_table t
                 WHERE t.user_id = l_my_tbl(i).user_id;

                IF ( l_count > 0 ) THEN
                -- raise error if rule violated
                    raise_application_error(-20001,'User id ['
                                                   || l_my_tbl(i).user_id
                                                   || '] has existing permissions. Null permission not allowed.');

                END IF;

            END IF;

        END LOOP;
        
    END AFTER STATEMENT;
END;