[burningmoney]

I am having some problems with my trigger..
Basically the trigger is there to inform the user that they are entering an item that already exists on the database.

The problem however is that the Trigger wont function, it will not allow any item to be entered into the database (even if this is the first item)
It does retrun a message if the same item exists on the database, but it still returns errors.
And even if I dont have the same thing on the database, the system returns a unique Constraint violation.
I am using a procedure to addnew items. But when I remove the trigger it functions perfectly so the error must be here.

I would really be gratefull to anyone who can help me understand this problem. To me the code is logical, obviously not to the system.

Heres the code:
---------------
CREATE OR REPLACE TRIGGER UPDATEITEM
AFTER INSERT OR UPDATE OF title ON ITEM
DECLARE

CURSOR ist_item IS
select title from item where itemno = (select max(itemno) from item);

-------------Selects newest Item (the one that has been inserted

CURSOR sst_item IS
select itemno from item where itemno = (select max(itemno) from item);

v_count number;
old_count number;
num number;
new_title varchar(20);
old_title varchar(20);
errors_main EXCEPTION;
BEGIN
OPEN ist_item;
OPEN sst_item;
FETCH sst_item INTO num;

if num> 0 then -----------checks if anything is in the record yet.

FETCH ist_item INTO new_title;

select title into old_title from item where title= new_title and itemno<> num; -----looks at another item in the database with the same title ---- --------- but not the same number


if old_title=new_title THEN
RAISE_APPLICATION_ERROR (-20000, 'ITEM ALREADY EXISTS IN DATABASE');

END IF;
END IF;
END;
/
SHOW ERROR
---------------------

[cvandemaele]

Not sure what you are trying to accomplish.

1. Your trigger is fired after each STATEMENT, not after each ROW. You can insert/update millions rows into the ITEM table with a single statement. Wouldn't you like to check each insert/update ?

2. You are opening cursors, but not closing them.

3. As for as I can understand, you are assuming that the row with the highest ITEMNO is the one that you have been inserting/updating. Does that assumption really hold ?

To my point of view, forget about the trigger and just add a unique constraint on column TITLE in table ITEM. That is exactly what they are built for.

Good luck.

[burningmoney]

What I am trying to achive is:
- Making sure that each item is unique, if someone is trying to put in another item of the same title, this trigger should find the existing item and update the quantity.
(At the moment there is an error message in place of the 'update table' statement) - I am trying to get the basics working.

So the trigger will look for items with the same name, then fire when it has found an existing item.

Thats what this statement does:
select title into old_title from item where title= new_title and itemno<> num;
Looks for a title that is the same as the new title that has been entered but is not the same record number.

The MAX(itemno) is to show the newest record. (The item is stored in a sequential fashion, thus the highest in the database will also be the newest entry and the one that needs to be compared with the rest of the database)

I know the Trigger looks a little sketchy, its just because I have been playing round with it so much, that some of the Close Cursor statement have been deleted, but even with them it doesnt work.

Big thanks for looking at this post, have you any further ideas...

[cvandemaele]

The MAX(itemno) is to show the newest record. (The item is stored in a sequential fashion, thus the highest in the database will also be the newest entry and the one that needs to be compared with the rest of the database)

This is a very tricky assumption you make. Did you think about updates ? Suppose your table contains two rows :
ItemNo Title
1 First_title
2 Second_title

Now suppose somebody updates row identified by ItemNo 1, and sets the title colum to "Second_Title". Does your trigger still do the job ? What about bulk inserts ? Did you notice that your trigger only fires after each statement. I can do a million inserts in your table and only have the trigger fired once...

You also probably will run into the notorious "ORA-04091 table is mutating".

The only proper way to enforce uniqueness is to use a constraint. But, if you still want to use a trigger, you might want to try (you need Oracle version 8.1.5 or higher):

create or replace trigger UPDATEITEM
AFTER INSERT OR UPDATE OF title ON ITEM
for each row -- verifies all inserted or updated rows !!
declare
pragma autonomous_transaction; -- circumvent ORA-04091
v_count number;
begin
select count(*) into v_count from item where title = :new_title;
if v_count > 0 then raise_application_error(-20000,'ITEM ALREADY EXISTS IN DATABASE'); end if;
end;

[burningmoney]

Thats a really good point. (and I didnt consider the fact that it only fires once)

However: I think I can get away with it, in any case, because the entire concept of adding an item is done via procedures (it has to be, its part of the assignment) Therefore the issue of bulk loading never applies (as far as this scenario goes)
Although at a later stage I do want to make some compensations for bulk loading, but it is not really the highest priority.


The reason why this entire trigger looks show slip-shot is because the strength of the scenario that we are doing, realistically the system cannot be implemented in a real organisation, however it has to be implemented with the scenario in mind, and we cant make our own assumptions.

Thanks again for the reply, I am begining to understand how this is supposed to work.