Re: Help with triggers

Guess automatic conversion is not being done and you need temp := 'SSCA-'||to_char(temp,'000009') -- or something similar.

Hth,

Sybrand Bakker, Oracle DBA

"Jeff Boyer" <jdboyer@(remove)icomproductions.ca> wrote in message news:94kvtn$s1l$1_at_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:47:52 CST