Re: Help with triggers

From: Jeff Boyer <jdboyer_at_(remove)icomproductions.ca>
Date: Tue, 23 Jan 2001 15:11:59 -0700
Message-ID: <94kvtn$s1l$1@news3.cadvision.com>

I have been playing around with my old code and seem to have made some progress. I came up with almost the same solution as you except that it is giving me an error when it tries to join Temp with the 'SSCA-'.

When I use your code I get the error. When I create the trigger it asks for the value of temp. When I give it a value it gives me this error:

LINE/COL ERROR

-------- -----------------------------------------------------------------
8/21     PLS-00103: Encountered the symbol "6" when expecting one of the
         following:
         . ( * @ % & = - + ; < / > at in mod not rem
         <an exponent (**)> <> or != or ~= >= <= <> and or like
         between is null is not || is dangling
         The symbol "*" was substituted for "6" to continue.

The only way I can get past this is to substitute the & with a +. When I do this the trigger is created with no errors. However, when an insert is done I get an error because the trigger is trying add the value of temp to 'SSCA' which can't be done because they are not numbers. Any suggestions?

Thanks for your help
Jeff

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:t6rvun98q2gm4c_at_beta-news.demon.nl...
> Corrected code
> CREATE OR REPLACE TRIGGER MY_TEST_TRG
> BEFORE INSERT on Company_reg
> FOR EACH ROW
> DECLARE
> Temp Varchar2(30);
> BEGIN
> select TEST_SEQ2.NEXTVAL
> into temp
> from dual;

>


>     Temp = 'SSCA-' & Temp

>      INSERT INTO Company_Reg (Company_Id) Values (Temp);

> END MY_TEST_TRG;
> /

>

> Hth,

>

> Sybrand Bakker, Oracle DBA

>
>
>

> "Jeff Boyer" <jdboyer@(remove)icomproductions.ca> wrote in message
> news:94kr8o$q18$1_at_news3.cadvision.com...
> > I have to start off by saying I am new to triggers so please bare with
 me.
> > I have written a trigger that acts as an auto-numbering system similar
 to
> > the auto number of access. What I am trying to do now is append the
 letters
> > 'SSCA-' to the beginning of my generated number. Below is the code I
 am
> > using, can some take a look and tell me if I am on the right track.
 Since
 I
> > have added the text to append to the auto number I am generating errors
 when
> > I create the trigger.
> >
> > Working trigger:
> > CREATE OR REPLACE TRIGGER MY_TEST_TRG
> > BEFORE INSERT on Company_reg
> > FOR EACH ROW
> > BEGIN
> > SELECT TEST_SEQ2.NEXTVAL
> > INTO :NEW.Company_Id;
> > END MY_TEST_TRG;
> > /
> >
> > New trigger that doesn't work:
> > CREATE OR REPLACE TRIGGER MY_TEST_TRG
> > BEFORE INSERT on Company_reg
> > FOR EACH ROW
> > DECLARE
> > Temp Varchar2(30);
> > BEGIN
> > Temp = SELECT TEST_SEQ2.NEXTVAL
> > Temp = 'SSCA-' & Temp
> > INSERT INTO Company_Reg (Company_Id) Values (Temp);
> > END MY_TEST_TRG;
> > /
> >
> >
> > My sequence that generates the auto number is as follows:
> > CREATE SEQUENCE TEST_SEQ2 start with 0001 increment by 1 minvalue 0001;
> >
> > Any help that can be given will be greatly appreciated,
> > Thanks
> > Jeff
> >
> >

>
> Received on Tue Jan 23 2001 - 16:11:59 CST