Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: INSTEAD OF INSERT trigger and SELECT

Re: INSTEAD OF INSERT trigger and SELECT

From: Volker Apelt <gq437x_at_yahoo.de>
Date: Mon, 17 Mar 2003 18:45:51 +0100
Message-ID: <lgfzplc2eo.fsf@mu.biosolveit.local>

Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> writes:

> On Fri, 14 Mar 2003 15:25:55 +0100, Volker Apelt <gq437x_at_yahoo.de>
> wrote:
>
>>Concept:
>>We insert into one table from many clients via mass inserts.
>>Each row is unique and part of one unique constraint.
[a trigger throws re-INSERTs of already existing rows away]

[I re-arranged parts of your answer.
 Please, see my message to Galen Boyer, too.]

>- use deferred constraints

 I don't see how this could solve the problem. AFAIK this will just defer the problem until the end of the transaction.

Lets say, I defer the constraints, what should happen at the end of the transaction? First insert with dropped constraint then delete second occurrence of duplicates?

Hmm, I could add one additional unique index on the entire row and deferre the indexes on ID and DATA.

True duplicates, where the pair (ID+DATA) is already in the database, will raise the exception in the INSTEAD OF trigger. The duplicates with an invalid ID will raise the DUP_VAL_ON_INDEX at the end of the transaction when the other two indices are checked.

Is that what you had in mind?
If not, could you comment with more details, please?

> Hence you would better either
>- dump the unique constraint alltogether as you can't guarantee
> uniqueness.

That is not an option. It would throw away one key feature of the database, the data integrity.
The current problem is that the database is rejecting a valid duplicate.

>- make the rows artificially unique

So, you suggest? Add further columns? ID and DATA are already unique by them self.
And other tables referring to indentical data in this table should refer to the same row. The row should be shared. The 'select count(*)' part in my trigger is meant to tell me the difference between acceptable identical rows where (ID+DATA) is already in the db and invalid ID calculations on the client. It is meant as a debugging helper.

Is there a way to know which constraint is violated? The select was required, because I don't know which was violated.

> you would better redesign the concept, instead of designing mickey
> mouse code to fight symptoms.

[snip]
> All of these alternatives are preferred to the crap you have now.

Here is the reference, where I took the idea of throwing duplicate INSERTs away in an instead of trigger.

# -- start of citation
From: Thomas Kyte (tkyte_at_us.oracle.com)
Subject: Re: ignore_dup_row or ignore_dup_key Newsgroups: comp.databases.oracle.server Date: 1999/09/27

[snip]

tkyte_at_8.0> create or replace trigger t_trigger   2 INSTEAD OF INSERT ON T
  3 for each row
  4 begin
  5 insert into t_table values ( :new.x );   6 exception

  7          when DUP_VAL_ON_INDEX then
  8                  null;

  9 end;
 10 /
Trigger created.

[snip]
# -- end of citation

He is not compaining or advising against this kind of trigger. So, could you explain, why you dislike it? Or else, what you dislike?

Thanks,

Volker

-- 
Volker Apelt                   
Received on Mon Mar 17 2003 - 11:45:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US