I have a table with the following values:
| user_id | permission |
|---|---|
| 1 | 2 |
| 2 | 4 |
| 1 | 4 |
| 3 | null |
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?
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.
A compound trigger will allow one to:
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;