Re: Problem with triggers

From: Joe Rogers <joer_at_renewal-iis.com>
Date: 1997/08/14
Message-ID: <33F367B5.11BE65BF@renewal-iis.com>#1/1

--------------05AA4219B6CED6E937EAE11D
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

turnerk wrote:

> I am running Oracle 7.3.2.2 on Netware 4.1 and am having problems
> creating
> triggers. Here is the table and the trigger definition I am trying to
> use:
>
> create table trigger_test_table
> (
> id number(16,0) not null,
> name varchar2(128)
> );
>
> create sequence test_trigger_id
> start with 1
> increment by 1
> maxvalue 999999999999999;
>
> create or replace trigger ttt_insert_trigger
> before insert on trigger_test_table for each row
> declare
> new_id number;
> begin
> select test_trigger_id.nextval into new_id from dual;
> :new.id := new_id;
> end ttt_insert_trigger;
>
> The table and the sequence create just fine, but I can not create the
> trigger.
> The only error message that SQL*Plus(3.3) gives me is an error
> compiling the
> trigger. I type: "show errors" and it says: "no errors".
>
> This seems pretty straightforward to me. Does anyone see what might
> be wrong?
> Does anyone have any better way of creating triggers since SQL*Plus
> does not
> give the needed help?
>
> Thanks for your time.
>
> K. Wade Turner
> turnerk_at_dimensional.com

Put a forward slash ( / ) on a line by itself after the last line of the trigger, so it knows the block is complete.

create or replace trigger ttt_insert_trigger before insert on trigger_test_table for each row declare

new_id number;
begin

select test_trigger_id.nextval into new_id from dual; :new.id := new_id;
end ttt_insert_trigger;
/

It complies fine, and seems to work the way you probably intended.

One other note, "show errors" doesn't work with triggers. You have to use "select * from user_errors" to see what went wrong.

--
Joe Rogers
Intelligent Information Systems, Inc.
joer_at_renewal-iis.com


--------------05AA4219B6CED6E937EAE11D
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>
turnerk wrote:
<BLOCKQUOTE TYPE=CITE>I am running Oracle 7.3.2.2 on Netware 4.1 and am
having problems creating
<BR>triggers.&nbsp; Here is the table and the trigger definition I am trying
to use:

<P>create table trigger_test_table
<BR>(
<BR>&nbsp;&nbsp; id number(16,0) not null,
<BR>&nbsp;&nbsp; name varchar2(128)
<BR>);

<P>create sequence test_trigger_id
<BR>&nbsp;&nbsp; start with 1
<BR>&nbsp;&nbsp; increment by 1
<BR>&nbsp;&nbsp; maxvalue 999999999999999;

<P>create or replace trigger ttt_insert_trigger
<BR>before insert on trigger_test_table for each row
<BR>declare
<BR>&nbsp;&nbsp; new_id number;
<BR>begin
<BR>&nbsp;&nbsp; select test_trigger_id.nextval into new_id from dual;
<BR>&nbsp;&nbsp; :new.id := new_id;
<BR>end ttt_insert_trigger;

<P>The table and the sequence create just fine, but I can not create the
trigger.
<BR>The only error message that SQL*Plus(3.3) gives me is an error compiling
the
<BR>trigger.&nbsp; I type: "show errors" and it says: "no errors".

<P>This seems pretty straightforward to me.&nbsp; Does anyone see what
might be wrong?
<BR>Does anyone have any better way of creating triggers since SQL*Plus
does not
<BR>give the needed help?

<P>Thanks for your time.

<P>K. Wade Turner
<BR>turnerk_at_dimensional.com</BLOCKQUOTE>
&nbsp;

<P>Put a forward slash ( / ) on a line by itself after the last line of
the trigger, so it knows the block is complete.

<P><TT>create or replace trigger ttt_insert_trigger</TT>
<BR><TT>before insert on trigger_test_table for each row</TT>
<BR><TT>declare</TT>
<BR><TT>&nbsp;&nbsp; new_id number;</TT>
<BR><TT>begin</TT>
<BR><TT>&nbsp;&nbsp; select test_trigger_id.nextval into new_id from dual;</TT>
<BR><TT>&nbsp;&nbsp; :new.id := new_id;</TT>
<BR><TT>end ttt_insert_trigger;</TT>
<BR><TT>/</TT>

<P>It complies fine, and seems to work the way you probably intended.

<P>One other note, <TT>"show errors"</TT> doesn't work with triggers.&nbsp;
You have to use <TT>"select * from user_errors"</TT> to see what went wrong.
<BR>&nbsp;

<P>--
<BR>Joe Rogers
<BR>Intelligent Information Systems, Inc.
<BR>joer_at_renewal-iis.com
<BR>&nbsp;</HTML>

--------------05AA4219B6CED6E937EAE11D--

Received on Thu Aug 14 1997 - 00:00:00 CDT