| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: What's wrong with this SQL statement?
A copy of this was sent to Troy Perchotte <max_at_headroom.com>
(if that email address didn't require changing)
On Thu, 18 Jun 1998 17:33:40 -0700, you wrote:
>I would like to have oracle update the item_id field (kind of like the
>counter field in ms-access) whenever a new record is created. But when
>I try to create my trigger to do this, I receive the following error:
>
>"ora6552: compilation unit analysis terminated"
>"ora6553: pls:320: the declaration of the type of this expression"
>
>I have created a sequence called: inv_field_items_sequence
>
>CREATE OR REPLACE TRIGGER "DCI".INV_FIELD_ITEMS_TRIGGER AFTER INSERT ON
>"DCI"."INV_FIELD_ITEMS" for each row
>begin
> update dci.inv_field_items
> set dci.inv_field_items.item_id=dci.inv_field_items_sequence.nextval
> where dci.inv_field_items.item_id = :NEW.item_id
You are missing a semicolon right here -------------^
But, even if it compiled, it won't work. To assign a sequence like this, you should code:
...
BEFORE insert ont inv_field_items
for each row
begin
select dci.inv_field_items_sequence.nextval into :new.item_id
from dual;
end;
the way you are doing it would cause the mutating table error... You cannot read/write to the table the trigger is fired on when it is a row level trigger...
Just write to the :new values, that will change the row the trigger is firing on.
also, you want to use a BEFORE trigger, not an AFTER..
>end;
>
>Any idea on what I am do missing or doing wrong?
>
>Troy Perchotte
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jun 18 1998 - 22:14:11 CDT
 |
 |