Help with triggers

Help with triggers

Post by Jeff Boye » Thu, 25 Jan 2001 05:52:32



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

 
 
 

Help with triggers

Post by Sybrand Bakke » Thu, 25 Jan 2001 07:06:05


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


Quote:> 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


 
 
 

Help with triggers

Post by Jeff Boye » Thu, 25 Jan 2001 07:11:59


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:

         <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


> 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



> > 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

 
 
 

Help with triggers

Post by Van Messne » Thu, 25 Jan 2001 08:11:30


Oracle says it is finding a number 6 in your code.  Neither the code you
posted nor Sybrand's has a 6.  It may be a simple typing mistake.  Or post
you current code so we can see where the 6 is coming from.


> 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:

>          <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



> > 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



> > > 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

 
 
 

Help with triggers

Post by Sybrand Bakke » Thu, 25 Jan 2001 07:47:52


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

Hth,

Sybrand Bakker, Oracle DBA


> 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:

>          <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



> > 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



> > > 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

 
 
 

Help with triggers

Post by RHC » Thu, 25 Jan 2001 08:30:07


Try this....

CREATE OR REPLACE TRIGGER MY_TEST_TRG

Quote:>   BEFORE INSERT on Company_reg
>    FOR EACH ROW
>    DECLARE
>    Temp Varchar2(30);

     myNum pls_integer;

Quote:>    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


> 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/
 
 
 

Help with triggers

Post by Jeff Boye » Thu, 25 Jan 2001 08:41:16


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!


> 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


> > 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/

 
 
 

1. Trigger Newbie... Help with Trigger

Hi all,

Pardon the newbie ignorance on my part in advance, I do appreciate your
help.  The documentation has not made clear if, while programming a trigger,
the T-SQL statement can assume we are working with the current record in the
specified table, meaning that record which has just been inserted only.

What I am trying to accomplish is to fire a trigger, after an insert takes
place, check the data in one field, and if it contains a certain value,
populate another field with a value.  Sounds simple enough.  For certain
reasons, it has to take place after all other insert operations are
complete.  What I have would be something like this:

CREATE Trigger UpdateInvoiceNumber ON INVMAST
AFTER INSERT
AS
UPDATE IVMAST SET IVMAST.IVNO = 'K' & [IVMAST]![IVNO]
WHERE (((Left([IVMAST]![IVCO],1))='I'))

Go

I know this is not exactly right, but somewhat what I wish to accomplish.
Anyway, my problem is I am unsure if this would execute on all records in
IVMAST or just the one that was just inserted.  As stated above, the one
just inserted is the only one I wish to perform this operation on.

Any help or suggestions are greatly appreciated!

Thanks in Advance

SM

2. sp_repldropcolumn fails if default constraint exists

3. Help: Invalid Trigger Error on Valid trigger

4. Insert where not exists

5. Help-Sybase trigger to Informix trigger

6. ATTN Howard: Return Of Complicated Export q

7. Help with Trigger

8. User input (Paradox 8)

9. Urgent help with Triggers in MS-SQL 2000

10. Newbie help w/ trigger problem

11. HELP: SQL Trigger

12. Help with Trigger problem

13. Need help w/trigger