CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Webowiec
Webowiec

Reputation: 15

PL/SQL trigger doesn't work properly

my sql trigger throws an error when added login exists in system but if not, no row is inserted.

Can somebody tell, why?

create or replace trigger user_login_exist_validator
before insert on USERS
for each row
declare
login varchar2(32 char) := :new.USER_LOGIN;
login_exists number(1,0) := 0;

begin
  select 1 into login_exists from USERS where USER_LOGIN=login;
if login_exists > 0
then
 RAISE_APPLICATION_ERROR(-20666, 'Użytkownik ' || login || ' już istnieje w systemie!!!');
end if;

end;

Upvotes: 0

Views: 172

Answers (1)

Peter M.
Peter M.

Reputation: 713

Looks like a mutating table error.

You are trying to read the same database table while you're already in the act of modifying data in it.

It might be better to take away the trigger. You can catch the exception and handle it where you do the insert statement.

https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/07_errs.htm

Upvotes: 1

Related Questions