Re: Hepl with Triggers

From: John Alexander <jalexander_at_summitsoftwaredesign.com>
Date: Tue, 23 Jan 2001 23:26:28 GMT
Message-ID: <Eqob6.3857$Tl3.1210652_at_typhoon.tampabay.rr.com>


[Quoted] It's a good thing the 2nd one didn't work - the code reads that it will insert a record into company_reg every time a record is inserted into company_reg, which gives you an infinitely running process.

The first trigger is good, just add the string: CREATE OR REPLACE TRIGGER MY_TEST_TRG
  BEFORE INSERT on Company_reg
   FOR EACH ROW
   BEGIN
   SELECT 'SSCA-'||TO_CHAR(TEST_SEQ2.NEXTVAL)    INTO :NEW.Company_Id
   FROM DUAL;
  END MY_TEST_TRG;
/

John Alexander
www.SummitSoftwareDesign.com
St. Petersburg, FL
Jeff Boyer <jdboyer_at_(remove)icomproductions.ca> wrote in message news:94kraj$q60$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 Wed Jan 24 2001 - 00:26:28 CET

Original text of this message