4 Replies Latest reply: Feb 21, 2013 5:39 AM by HeSi9466 RSS

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

    HeSi9466
      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
          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
            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
              >
              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
                Many thanks for your help.

                You were right and my problem is solved now.