CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
user4194287
user4194287

Reputation:

Checking if every table object has a different name

I have a Users table, each User has a identifier, a name and some other fields. I need to check if there is another user with the same name after inserting or updating, so I made this trigger:

CREATE OR REPLACE TRIGGER user_name
  BEFORE
  INSERT OR UPDATE
  ON Users
  FOR EACH ROW
DECLARE
  count INTEGER;
BEGIN
 SELECT COUNT(*) INTO count FROM Users WHERE name = :new.name AND idUser <> :new.idUser;
  IF count > 0
    THEN raise_application_error(-20007, 'There is already another user with the same name');
  END IF;
END;

It seems to work when inserting new users, but it doesn't when I update them, it looks like it "ignores" the idUser check so it always fails as it finds the same user with the same name. Why is this happening? Thank you

Upvotes: 1

Views: 46

Answers (1)

Moudiz
Moudiz

Reputation: 7377

In triggers , there are 3 states as you know inserts update and delete , In update states there are new values and old values , I mean when you update a column you , you will replace the old value with new one , in trigger you can use the old and the new value of the column .. Please check this example

CREATE or REPLACE TRIGGER test001
  AFTER INSERT OR UPDATE OR DELETE ON tabletest001


DECLARE
      Operation       NUMBER;
      CustomerCode    CHAR(10 BYTE);
BEGIN


IF INSERTING THEN 
  Operation := 1;
  CustomerCode := :new.field1;
END IF;

IF UPDATING THEN 
  Operation := 2;
  CustomerCode := :old.field1;
END IF;    

// DO SOMETHING ...

EXCEPTION
    WHEN OTHERS THEN ErrorCode := SQLCODE;

END;
/

in the above example , in update state I used old.value of the column , so in your example you should check the old value not new , please try it 

if updating then 

SELECT COUNT(*) INTO count FROM Users WHERE name = :old.name AND idUser <> :old.idUser;
  IF count > 0
    THEN raise_application_error(-20007, 'There is already another user with the same name');

end if

however as in the comments adviced you is to use primary key, however my answer is to give you understanding to triggers

Upvotes: 1

Related Questions