Problem getting the oracle trigger to execute

725425

I 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?

jflack

Why are you trying to enforce uniqueness with a trigger? Triggers can't do it - they can't see any other row but the one being updated. When you are comparing :new with :old, you are comparing the former value of the column with the new value of the column on the same row. Which only makes sense during an UPDATE, not an INSERT.

Use an unique constraint instead.

725425

So there would not be any way to use a trigger to do this? Maybe not by using row within the for loop, but some other way? I could use a unique constraint for the multiple columns but seems like the requirements wanted it to be done using the trigger ( and throw an error or exception when user tries to insert the same values again) rather than using unique constraint... If it is not possible though, then I will have to do it using the unique constraint...

Edited by: user11963881 on Oct 1, 2009 11:04 AM

-K-

It is almost impossible, and at least a very bad thing to do. Use the constraint.

In the future, post your questions under the SQL And PL/SQL forum, as this one is only for the SQL Developer tool.

Thanks,
K.