This discussion is archived
4 Replies Latest reply: Feb 21, 2013 3:39 AM by HeSi9466 RSS

Trigger to inser a special sepence into a field does not work

HeSi9466 Newbie
Currently Being Moderated
Hi,

I have a trigger which should insert a special seqence into a fileld in a table but when i insert into the target table it does not work.
Any help is highly appreciated.

CREATE OR REPLACE TRIGGER BI_favcodenew1
BEFORE INSERT ON FAVE_CODE_FINAL FOR EACH ROW
DECLARE
V_SEQ NUMBER(12) := 0;
V_LENGTH NUMBER(2);
BEGIN

SELECT SEQ_SIXCODE.NEXTVAL
INTO V_SEQ
FROM dual;

Case length(V_SEQ)
when 1 then
V_SEQ := 'SWB000000'||V_SEQ;
when 2 then
V_SEQ := 'SWB00000'||V_SEQ;
when 3 then
V_SEQ := 'SWB0000'||V_SEQ;
when 4 then
V_SEQ := 'SWB000'||V_SEQ;
when 5 then
V_SEQ := 'SWB00'||V_SEQ;
when 6 then
V_SEQ := 'SWB0'||V_SEQ;
when 7 then
V_SEQ := 'SWB'||V_SEQ;
END CASE ;

SELECT V_SEQ
INTO :NEW.FAVCODE
FROM DUAL;

END;
/

INSERT INTO FAVE_CODE_FINAL
VALUES ('ok','OK','OK');


CREATE TABLE FAVE_CODE_FINAL
(
BANKID VARCHAR2(5 BYTE) NOT NULL,
CUSTOMERID VARCHAR2(12 BYTE) NOT NULL,
SIXID VARCHAR2(12 BYTE) NOT NULL,
FAVCODE VARCHAR2(20 BYTE)
)

If you need any more info just let me know.

Thanks in advance for the help.
  • 1. Re: Trigger to inser a special sepence into a field does not work
    riedelme Expert
    Currently Being Moderated
    Hesam wrote:
    Hi,

    I have a trigger which should insert a special seqence into a fileld in a table but when i insert into the target table it does not work.
    Any help is highly appreciated.
    This forum is for SQL*Developer issues. You will probably get a better answer asking this in the SQL and PL/SQL forum. However, since we are here anyway ...
  • 2. Re: Trigger to inser a special sepence into a field does not work
    MLBrown Journeyer
    Currently Being Moderated
    In your trigger change the declaration of the V_SEQ variable from a number to a varchar2 since you are adding varchar2 to the variable in the case statement:
    CREATE OR REPLACE TRIGGER BI_favcodenew1
       BEFORE INSERT
       ON FAVE_CODE_FINAL
       FOR EACH ROW
    DECLARE
       V_SEQ          varchar2 (12) := 0;
       V_LENGTH       NUMBER (2);
    BEGIN
       SELECT SEQ_SIXCODE.NEXTVAL INTO V_SEQ FROM DUAL;
    
       CASE LENGTH (V_SEQ)
          WHEN 1
          THEN
             V_SEQ       := 'SWB000000' || V_SEQ;
          WHEN 2
          THEN
             V_SEQ       := 'SWB00000' || V_SEQ;
          WHEN 3
          THEN
             V_SEQ       := 'SWB0000' || V_SEQ;
          WHEN 4
          THEN
             V_SEQ       := 'SWB000' || V_SEQ;
          WHEN 5
          THEN
             V_SEQ       := 'SWB00' || V_SEQ;
          WHEN 6
          THEN
             V_SEQ       := 'SWB0' || V_SEQ;
          WHEN 7
          THEN
             V_SEQ       := 'SWB' || V_SEQ;
       END CASE;
    
       SELECT V_SEQ INTO :NEW.FAVCODE FROM DUAL;
    END;
    /
    Also, declare your column names in your insert in order for it to execute:
    INSERT INTO FAVE_CODE_FINAL (BANKID, CUSTOMERID, SIXID)
    VALUES ('ok','OK','OK');
    Now, it should work, but Riedelme is right and you should post these questions in {forum:id=75} next time.
  • 3. Re: Trigger to inser a special sepence into a field does not work
    rp0428 Guru
    Currently Being Moderated
    >
    SELECT V_SEQ
    INTO :NEW.FAVCODE
    FROM DUAL;
    >
    What is that supposed to be?

    You don't need to change the datatype. Just assign the value in your case statement like this:
    :NEW.FAVCODE := 'SWB000000' || V_SEQ;
    And since you don't have an ELSE in the CASE statement what do you think will happen if the sequence is greater than 7?

    Fix your bug. It is poor practice to write code that doesn't have a 'catch all' to cover ALL possibilities.
  • 4. Re: Trigger to inser a special sepence into a field does not work
    HeSi9466 Newbie
    Currently Being Moderated
    Many thanks for your help.

    You were right and my problem is solved now.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points