Hi All,
I am trying to build in some logic to handle duplicates, and in trying to implement this logic, I have hit the
ORA 04091 error head on. I have read through many posts (including Toms'), but I'm not sure that is how I should do this. Maybe somebody has a better idea than I do...
I have a table that holds results information, and I want to mark a result as a duplicate if it already exists in the table. The problem is that I can't check the table from the trigger. I had thought about doing it in the PLSQL code that does the insert, but it seemed to be more efficient to do at the trigger level. The data that is duplicated
is not part of the PK, and is occasionally expected to have duplicates. I load this data in from a CSV into a stage table, and then I do a insert into ... (select * from) - not quite, but didn't want to type all the columns - So that is why I chose to use the trigger to test.
select
count(*)
into
v_dup_cnt
from
table
where
name = :new.Name;
Then I use the value of count to set the status (in another table)
IF v_dup_cnt > 0 then
:new.duplicate := 'Y';
End If;
Any thoughts would be greatly appreciated,
Corey