Problem getting the oracle trigger to execute
725425Oct 1 2009 — edited Oct 2 2009I am trying to create a trigger that will execute whenever an insert is being made on an already existing unique value. So, for example, if my database table named ORG_ALIAS has an alias entry, trying to insert another alias with same value, type and assigning authority should throuw an error:
Here is my code I put in sql developer to create the trigger:
CREATE TRIGGER TRIGGER1
BEFORE INSERT ON ORG_ALIAS
referencing new as new old as old
for each row
BEGIN
if ((:new.ALIAS_VALUE = :old.ALIAS_VALUE) and (:new.ALIAS_TYPE = :old.ALIAS_TYPE) and (:new.ASSIGNING_AUTHORITY = :old.ASSIGNING_AUTHORITY)) then
raise_application_error (-20999, 'NPI should be unique');
end if;
end;
/
when i run it, it shows that the trigger has been created successfully and with no errors. But when I try to insert another duplicate entry into the table with the same value, type and assigning authority, it allows me to insert that entry into the table instead of erroring out.
I am not sure what am I missing here?