Re: Help with triggers

Actually what the problem is, was how I was trying to concatinate the vlaues. The || worked and everything is working great now.

Thanks alot for all your help
Jeff

P.S.
When I had the & instead of the || SQL*PLUS would ask me for the value of Temp before creating the trigger. The 6 was just a value that I entered when it prompted me to enter a value.
Thanks again, you all have been a great help!

"RHC" <rclarence_at_juno.com> wrote in message news:t6s4vvqsb1u51_at_corp.supernews.com...
> Try this....
>
> CREATE OR REPLACE TRIGGER MY_TEST_TRG
> > BEFORE INSERT on Company_reg
> > FOR EACH ROW
> > DECLARE
> > Temp Varchar2(30);

   myNum pls_integer;
> > BEGIN
> > SELECT TEST_SEQ2.NEXTVAL into myNum from dual;
> > Temp := 'SSCA-'||to_char(myNum);
> > INSERT INTO Company_Reg (Company_Id) Values (Temp);
> > END MY_TEST_TRG;
>
> Also you should have an exception block for the unlikely but possible
> invalid number exception.
>
> HTH
> RHC
>
>
>
>
> Jeff Boyer wrote:
> >
> >
> > 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
> >
> >
>
>
> --
> Posted via CNET Help.com
> http://www.help.com/
Received on Tue Jan 23 2001 - 17:41:16 CST