Re: Help with triggers

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 23 Jan 2001 23:06:05 +0100
Message-ID: <t6rvun98q2gm4c@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:06:05 CST