Hi All,
I have to avoid duplicate entry in a table inside a trigger. I have this code. I don't want to raise application error because the insertion is called from inside external java code that connect to Oracle.
Unless we modify completly the code to take this into consideration
In short, how can I just prevent the insertion from getting done (kind of rollback)
I got the suggestion somewhere to use 2 triggers the second one deleting the unwanted rows, but is this really elegant?
CREATE OR REPLACE TRIGGER myTable_BI
before insert ON myTable
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
declare
vCount NUMBER;
begin
begin
select COUNT(*) into vCount
from myTable
where domain = :new.domain
and code = :new.code
and value = :new.value;
exception
when OTHERS then
vCount := 0;
end;
if vCount > 0 then --row already exists log the issue
insert into SP_ISSUES_LOG(ISSUE_TYPE ,ISSUE_DATE, ERROR)
values('DUPLICATE_ENTRY',sysdate,'Attempt of duplicate following row detected: Domain = '||:new.domain||', Code = '||:new.code||', Value'||:new.value);
---->here I need to be able to quit the insertion without raising an error
else
:new.id := 'ACAT' || trim (to_char (myTable_SEQ.nextval, '0000000000'));
end if;
end myTable_BI;
Right now this is not even working because I cannot insert NULL into column ID
Thanks