Re: Help with triggers

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/