0

I have such simple trigger. I have no compilation error. 

CREATE OR replace TRIGGER test_triger
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW
DECLARE
    varii NUMBER;
BEGIN
    SELECT Count(dur.id)
    INTO   varii
    FROM   users dur
    WHERE  dur.id = :old.id
            OR dur.id = :new.id;

    IF( varii > 0 ) THEN
      Raise_application_error(-20001, 'error!, ');
    END IF;
END;

When I invoke update I have such error:

TEST.USERS is mutating, trigger/function may not see it.

what does it mean?

ORA-04091, ORA-06512 and ORA-04088 Errors.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
grep
  • 5,465
  • 12
  • 60
  • 112
  • You're selecting from the same table that you're updating. – Sathyajith Bhat Jan 08 '15 at 10:41
  • @Sathya BEFORE update, I want to see if everything is well. If everything is not ok I throw error. why this is problem? Cant I do that? and solution? – grep Jan 08 '15 at 10:49
  • what do you mean by 'everything is well'? Why do you need to test? What do you want to test? – Sathyajith Bhat Jan 08 '15 at 11:21
  • I have graph of user roles. when administrator updates roles, he/she should not manage to make loop. For this I wrote trigger. BEFORE UPDATE I check if there will be loop, if admin execute update command. Is not everything clear? – grep Jan 08 '15 at 11:25

0 Answers0