-1

I have trigger

 BEFORE  UPDATE  ON  USER_ROLES

I know that if I write "SELECT FROM USER_ROLES" in the trigger, I will have "mutating, trigger/function error". This is true, no problem! If I select from the same table that I am updating, I will have that error - I know that, that's nice.

But I should select (there is no way) the same table in order to detect whether user have access to update the table or not. For instance:

BEGIN
      /* check if update causes loop in tree */
      SELECT count(rol.id) INTO cnt
      FROM  USER_ROLES rol
      WHERE rol.id=:old.id
      START WITH rol.id = :new.parent_id
      CONNECT BY PRIOR rol.id = rol.parent_id ;

        IF( cnt > 0 )
                  THEN
                  ....
        END IF;

        ....
 END;

I know that there will be "mutating, trigger/function error" but where is solution? before update, how to check whether client have permission or not? Can I do the similar without writing "SELECT"?

grep
  • 5,465
  • 12
  • 60
  • 112
  • Duplicate of your previous question [Why am I getting table is mutating, trigger/function may not see it error?](http://stackoverflow.com/questions/27838141/why-am-i-getting-table-is-mutating-trigger-function-may-not-see-it-error) – Lalit Kumar B Jan 08 '15 at 11:20
  • this is not duplicate! I know why I get error! this is another question! please read question first! – grep Jan 08 '15 at 11:21
  • You do realize that select & update roles can be different? You can have select but not update. So your test will fail here. Why not just let it insert/update? If there's some exception handle it accordindly. Makes me believe this is a [xy problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem/66378#66378) – Sathyajith Bhat Jan 08 '15 at 11:24
  • I have graph of user roles. when administrator updates roles, he/she should not manage to make loop. For this I wrote trigger which checks if there will be loop or not If there will be loop with the update operation, trigger throws errors. BEFORE UPDATE I check if there will be loop, if admin execute update command. Is not everything clear? – grep Jan 08 '15 at 11:28
  • I found solution, DECLARE PRAGMA AUTONOMOUS_TRANSACTION; what do u think? In the doc there is written that it suspends transaction and creates new. When I start transaction from java business logic, there was not any new. – grep Jan 09 '15 at 07:30

1 Answers1

0
DECLARE  PRAGMA AUTONOMOUS_TRANSACTION;
grep
  • 5,465
  • 12
  • 60
  • 112