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

Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger & Sequence

Re: Trigger & Sequence

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Mon, 13 Feb 2006 21:05:50 -0500
Message-ID: <BaudnUOJDKpip2zeRVn-qw@comcast.com>

"Oliver Neumann" <tha.beast_at_web.de> wrote in message news:dsqr55$1s3$03$1_at_news.t-online.com...
: Hey there,
:
: i using Oracle Database 10g and im new to these sequences and
trigger-stuff.
: Now i got an issue that you might be able to help me with.
:
: i have created this sequence:
:
: CREATE SEQUENCE "AUTOINCRSEQ" INCREMENT BY 1 START WITH 1 NOMAXVALUE
: NOMINVALUE NOCYCLE NOORDER
:
:
: Afterwards i setted this trigger:
:
: create trigger "AUTOINC_OBJECT_TEAMS_CLIENT" before insert on
: OBJECT_TEAMS_CLIENT
: for each row begin
: select AUTOINCRSEQ.nextval into :new.CLIENT_ID from dual;
: end;/
: GO
:
:
:
:
: On both executions i receive this "error":
: Describe Error: Failed to execute EXPLAIN plan: ORA-00900: invalid SQL
: statement
:
: But he created both, the sequence and the trigger in the db. When i try to
: insert a row now in the trigger-sensitive Table OBJECT_TEAMS_CLIENT i
: receive this error:
:
: Error: Query(1/1) ORA-04098: trigger 'SYSMAN.AUTOINC_OBJECT_TEAMS_CLIENT'
is
: invalid and failed re-validation
:
:
:
: Is someone able to help me out with this ? I have these statements copied
: from a site (http://www.mitlinx.de/tricks/autoincrement_workaround.htm -
: german)...and the purpose of this sequence is an autoIncrementor for my
: PrimaryKey-Column in the table, named CLIENT_ID.
:
:
:
: any help is highly appreciated! thanks and greets,
: oliver
:
:

your complete command in SQL*Plus probably looked something like this:

SQL> create trigger "AUTOINC_OBJECT_TEAMS_CLIENT" before insert on   2 OBJECT_TEAMS_CLIENT
  3 for each row begin
  4 select AUTOINCRSEQ.nextval into :new.CLIENT_ID from dual;   5 end;/
  6 GO
  7 /

or perhaps you didn't type a slash on line 7, but typed RUN at the 'SQL>' prompt

here's a type and an syntax error that are likely contributing:

line 5: the '/' is incorrect -- in SQL*Plus, this must come as the first character of a line, not in any other position

line 6: the GO is not SQL*Plus or Oracle -- it's from that other tool. but since your '/' on 5 was misplaced, line 6 got included in the trigger source

daniel's suggestion is good, except SQL*Plus has it's own little quirk in that SHOW ERRORS does not work on trigger compilation errors to follow his advice and see the errors, you'll need to do a SELECT from the USER_ERRORS data dictionary view

and a bit of a formatting suggestion: BEGIN is the common way of starting a PL/SQL block, in triggers and otherwise. FOR EACH ROW is specific to trigger specifications. so, better to put the BEGIN on a line all by itself where it logically belongs, and where you'll see it in other PL/SQL code

++ mcs

++ mcs Received on Mon Feb 13 2006 - 20:05:50 CST

Original text of this message

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