creating a trigger

[bibyjord]

Hi,

we're trying to create a trigger to perform a check on existing rows in a table when an insert statement occurs...

but we don't know how the external variables are passed to this procedure....

the code that provide that control looks like:

create or replace trigger my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW

DECLARE
var1 CHAR(02);
var2 BOOLEAN;


BEGIN

SELECT col1 INTO var1
FROM my_table
WHERE col1 = :NEW.col1
AND col2 = :NEW.col2
AND col3 = :NEW.col3
AND col4 = :NEW.col4
AND col5 = :NEW.col5
AND col6 = :NEW.col6;

EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO my_table
VALUESNEW.col1, :NEW.col2, :NEW.col3,
:NEW.col4, :NEW.col5, :NEW.col6);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-my_err_num, 'my_err_msg');
END;

after compiling and validating this trigger we've tried to perform an insert statement but the check doesn't work...
so we're able to insert duplicated rows

Any idea will be appreciate.
Thanks for your support.
Best regards Roberto

 

[did02]

Instead using a trigger on your table, why do not use a WHERE EXISTS clause in your insert query ?

Did02

 

[bibyjord]

did02,

thanks for your answer; our purpose is to replace a pk that is used only on constraint level (not used for reads....)

we know that we can also solve this issue using a WHERE EXISTS clause but we want to perform a server-side check...

we also know that using trigger is a complicated and low-performance solution but we used to insert rows in that table only by script not by on-line programs or others....

 

[sem]

Your trigger (at least in this form) will not work because of well-known mutating problem. It will work only for single-row inserts, but even in this case you have to change your code a bit: instead of inserting one more row in the addition to currently being inserted, you need to prohibit inserting (raise error) for duplicate row. Remember that normal flow is to INSERT record even if trigger does nothing.

....
SELECT col1 INTO var1
FROM my_table
WHERE col1 = :NEW.col1
AND col2 = :NEW.col2
AND col3 = :NEW.col3
AND col4 = :NEW.col4
AND col5 = :NEW.col5
AND col6 = :NEW.col6;

raise dup_val_on_index;
exception
when too_many_rows then raise dup_val_on_index;

when no_data_found then null;

END;

BUt if your condition is really just a set of equations, the best way is to create unique constraint!

 

[bibyjord]

Sem,

thanks for your answer.
Our real aim is to eliminate the pk's index but not the constraint...

creating an unique constraint means also Oracle create an unique index...and is what we would like to discard.....

Is it correct that:
creating an unique constraint Oracle create also an unique index ?

In the mean time we're implementing you trigger's suggestion...

Thanks for you support !

 

[sem]

My trigger suggestion was just to show the most obvious error. Anyway it will not work properly unless you guarantee that only one seesion will insert data row by row. Any uncommited changes are invisible for other sessions so 2 sessions may successfully insert the same data. To have an index for uniqueness validation is NOT A BAD IDEA! Or are you going to perform full scan each time you need to insert a row?

 

[bibyjord]

Sem,
I don't want to perform a sistematics FTS on this table (it's also a big table....)

We have other indexes (unfortunatly non-unique) that help our statement to work properly....our consideration is about storage....we would like to save some disk space.

This table is quite big and to have a pk's index that isn't used should became a problem in the future...
so we're considering alternative object that provide the same constraint control without any storage implication.

Your trigger's consideration is correct....this solution doesn't suite our needs...

I'm starting to think this is not possible....

Thanks for your support.

 

[sem]

If you need a unique constraint based on a set of fields, why not to drop OTHER indexes and to create a really useful one? Just clear up the most suitable column order.

 

[bibyjord]

Sem,
we've just tried to follow this lineguide....
I'm trying to explain....

we 're working in RULE mode

My table has 6 cols, for example col1,col2,col3,col4,col5,col6

its pk has these fields' order:
col5,col6,col1,col4,col2,col1 (and isn't used for reads)

its non-unique index has these fields' order:
col5 (and IS USED in reads)

My table has 36 M of rows and grow constantly...

we have to maintain the constraint meaning but we would like to save some disk space....

ok...two questions:
1)why does oracle use the non-unique index (the first col is the same and the first is an unique index)?
2)do you have any idea about the col order in a new ipotetic unique-index ?

 

[sem]

Of course pk index size will be larger but it will also be utilized, as col5 is the first column. If your rule-based optimizer ignores index, use hint! Large size of index is the only drawback. In some situations (when you need only pk columns) you do not need to read table at all, just index (FFI)!

 

[karluk]

Your situation seems to be a candidate for using an index-organized table. The following is an excerpt from the Oracle 8i concepts manual:

"An index-organized table differs from an ordinary table because the data for the table is held in its associated index. Changes to the table data, such as adding new rows, updating rows, or deleting rows, result in updating the index."

"The index-organized table is like an ordinary table with an index on one or more of its columns, but instead of maintaining two separate storages for the table and the B-tree index, the database system maintains only a single B-tree index which contains both the encoded key value and the associated column values for the corresponding row."

 

[bibyjord]

Sem,

col5 is the first field of the pk !
Why does Oracle instead use the non-unique index to perform reads ?

so your suggest is to change our optimize parameter, isn't it ?

 

[karluk]

As Sem says, "Large size of index is the only drawback". Oracle prefers your nonunique index because it's much smaller than your primary key. If you dropped the nonunique index, Oracle would most likely use the primary key as the second best choice. Whether performance would suffer significantly is a matter for testing.

 

[sem]

You do not need to switch from RULE to CHOOSE, if you mean DATABASE parameter. I agree with Karluk regarding index usage. But even if your optimizer will insist on ignoring index you may force it by using INDEX hint on statement level. In some cases optimizer may ignore index if using > or < , but if you're sure that index may help, just add hint.

 

[bibyjord]

Sem, Karluk

thanks for your useful support; we're considering trasform this table in an index-organized one...

If our performance test will be ok, we will proceed soon.

Best Regards !