Problems with a Trigger

From: MJSobol <murray_sobol_at_dbcsmartsoftware.com>
Date: Fri, 21 May 2010 11:13:24 -0700 (PDT)
Message-ID: <82b56192-3452-43ee-b7eb-453ff51c014c_at_j27g2000vbp.googlegroups.com>



Environment: Oracle 10g2
Windows 32-bit, Standard Edition

I have created a Trigger which compiles OK. Here is the Trigger:
CREATE or replace TRIGGER au_s1_contract_misc_columnitem AFTER UPDATE ON s1_contract_misc_column_item FOR EACH ROW
DECLARE
  c_misc_column_description varchar2(50);

  quote                        char(1);

BEGIN
  quote := chr(39);

  c_misc_column_description :=

      CASE
          WHEN :new.misc_column_nbr = 1 THEN

'misc_column_description1'
WHEN :new.misc_column_nbr = 2 THEN
'misc_column_description2'
WHEN :new.misc_column_nbr = 3 THEN
'misc_column_description3'
WHEN :new.misc_column_nbr = 4 THEN
'misc_column_description4'
WHEN :new.misc_column_nbr = 5 THEN
'misc_column_description5'
WHEN :new.misc_column_nbr = 6 THEN
'misc_column_description6'
WHEN :new.misc_column_nbr = 7 THEN
'misc_column_description7'
WHEN :new.misc_column_nbr = 8 THEN
'misc_column_description8'
END;

  IF (:new.description <> :old.description) THEN     BEGIN
      EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' || c_misc_column_description || ' = ' || Quote || :new.description || Quote || 'WHERE ' || :new.misc_column_item_nbr || ' = s1_contract.misc_column_item_nbr1';

        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Updating misc_column_description1');
            dbms_output.put_line('SQLCODE= ' || SQLCODE);
            dbms_output.put_line('SQLERRM= ' || SQLERRM);
    END;     BEGIN
      EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' || c_misc_column_description || ' = ' || Quote || :new.description || Quote || 'WHERE ' || :new.misc_column_item_nbr || ' = s1_contract.misc_column_item_nbr2';
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Updating misc_column_description2');
            dbms_output.put_line('SQLCODE= ' || SQLCODE);
            dbms_output.put_line('SQLERRM= ' || SQLERRM);
    END;     BEGIN
      EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' || c_misc_column_description || ' = ' || Quote || :new.description || Quote || 'WHERE ' || :new.misc_column_item_nbr || ' = s1_contract.misc_column_item_nbr3';
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Updating misc_column_description3');
            dbms_output.put_line('SQLCODE= ' || SQLCODE);
            dbms_output.put_line('SQLERRM= ' || SQLERRM);
    END;     BEGIN
      EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' || c_misc_column_description || ' = ' || Quote || :new.description || Quote || 'WHERE ' || :new.misc_column_item_nbr || ' = s1_contract.misc_column_item_nbr4';
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Updating misc_column_description4');
            dbms_output.put_line('SQLCODE= ' || SQLCODE);
            dbms_output.put_line('SQLERRM= ' || SQLERRM);
    END;     BEGIN
      EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' || c_misc_column_description || ' = ' || Quote || :new.description || Quote || 'WHERE ' || :new.misc_column_item_nbr || ' = s1_contract.misc_column_item_nbr5';
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Updating misc_column_description5');
            dbms_output.put_line('SQLCODE= ' || SQLCODE);
            dbms_output.put_line('SQLERRM= ' || SQLERRM);
    END;     BEGIN
      EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' || c_misc_column_description || ' = ' || Quote || :new.description || Quote || 'WHERE ' || :new.misc_column_item_nbr || ' = s1_contract.misc_column_item_nbr6';
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Updating misc_column_description6');
            dbms_output.put_line('SQLCODE= ' || SQLCODE);
            dbms_output.put_line('SQLERRM= ' || SQLERRM);
    END;     BEGIN
      EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' || c_misc_column_description || ' = ' || Quote || :new.description || Quote || 'WHERE ' || :new.misc_column_item_nbr || ' = s1_contract.misc_column_item_nbr7';
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Updating misc_column_description7');
            dbms_output.put_line('SQLCODE= ' || SQLCODE);
            dbms_output.put_line('SQLERRM= ' || SQLERRM);
    END;     BEGIN
      EXECUTE IMMEDIATE 'UPDATE s1_contract SET ' || c_misc_column_description || ' = ' || Quote || :new.description || Quote || 'WHERE ' || :new.misc_column_item_nbr || ' = s1_contract.misc_column_item_nbr8';
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Updating misc_column_description8');
            dbms_output.put_line('SQLCODE= ' || SQLCODE);
            dbms_output.put_line('SQLERRM= ' || SQLERRM);
    END;   END IF; END;
/

Here is some sample data:
select * from s1_contract_misc_column_item; MISC_COLUMN_ITEM_NBR MISC_COLUMN_NBR SORT_ORDER DESCRIPTION

-------------------- --------------- ----------
--------------------------------------------------
                   1               1          7 Ayr
                   2               1          1 Arthur
                   3               1          3 Acton
                   4               1          4 Alma
                   5               1          2 Ariss
                   6               1          5 Arkell
                   7               1          6 Aberfoyle
                   8               2          1 Bayfield
                   9               3          1 Columbia
                  10               4          1 Decatur-1
                  11               5          1 Elmira
                  12               6          1 Flesherton

12 rows selected.

Here is the UPDATE statement I am issuing: update s1_contract_misc_column_item
set description = 'Decatur'
where misc_column_item_nbr = 10;

and here is the error I am getting:
Updating misc_column_description4
SQLCODE= 100
SQLERRM= ORA-01403: no data found

ORA-01403: no data found
ORA-06512: at "T1310.BU_S1_CONTRACT", line 1
ORA-04088: error during execution of trigger 'T1310.BU_S1_CONTRACT'

1 row updated.

I do not understand this error since there is data that SHOULD be updated:
CONTRACT_NBR MISC_COLUMN_ITEM_NBR1 MISC_COLUMN_ITEM_NBR2

MISC_COLUMN_ITEM_NBR3 MISC_COLUMN_ITEM_NBR4 MISC_COLUMN_ITEM_NBR5
MISC_COLUMN_ITEM_NBR6
MISC_COLUMN_DESCRIPTION1
MISC_COLUMN_DESCRIPTION2
MISC_COLUMN_DESCRIPTION3
MISC_COLUMN_DESCRIPTION4
MISC_COLUMN_DESCRIPTION5
MISC_COLUMN_DESCRIPTION6
------------ --------------------- ---------------------
--------------------- --------------------- ---------------------
---------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
         754                     5
8                     9                    10
11                    12

Ariss
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        7639

1
Ayr

        7640
1
Ayr

        7670
3
Acton

        7661
5
Ariss

        7919                     5
8
Ariss                                              Bayfield
        7922                     5
8
Ariss                                              Bayfield
        7923                     5
8
Ariss                                              Bayfield
        7973

5
Ariss
        7911                     3
8
Acton                                              Bayfield
        7912

5
Ariss
        7920                     1
8                     9                    10
11                    12

Ayr
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        7803                     1
8                     9                    10
11                    12

Ayr
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        7804                     5
8
Ariss                                              Bayfield
        8022                     5
8                     9                    10
11                    12

Ariss
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8028                     5
8                     9                    10
11                    12

Ariss
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8249                     5
8                     9                    10
11                    12

Ariss
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8138                     5
8                     9

10
Ariss
Bayfield
Columbia                                           Decatur
        8139                     5
8
Ariss                                              Bayfield
        8266                     5
8
Ariss                                              Bayfield
        8140                     3
8
Acton                                              Bayfield
        8151                     5
8                     9                    10
11                    12

Ariss
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8212                     5
8                     9                    10
11                    12

Ariss
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8268                     5
8
Ariss                                              Bayfield
        8269                     5
8
Ariss                                              Bayfield
        8518

5
Ariss

        8519
5
Ariss

        8525
5
Ariss

        8538
5
Ariss

        8539
5
Ariss

        8540
5
Ariss

        8544
5
Ariss

        8546
5
Ariss

        8588
2
Arthur

        8626                     2
8                     9                    10
11                    12

Arthur
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8627                     2
8                     9                    10
11                    12

Arthur
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8677                     2
8
Arthur                                             Bayfield
        8693                     2
8
Arthur                                             Bayfield
        8698                     2
8                     9                    10
11                    12

Arthur
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8699                     2
8                     9                    10
11                    12

Arthur
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8700                     2
8                     9                    10
11                    12

Arthur
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8701                     2
8                     9                    10
11                    12

Arthur
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8702                     2
8                     9                    10
11                    12

Arthur
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8703                     2
8                     9                    10
11                    12

Arthur
Bayfield
Columbia
Decatur
Elmira                                             Flesherton
        8687                     2
8
Arthur                                             Bayfield

45 rows selected.

I.E. any value of ‘Decatur’ in column MISC_COLUMN_DESCRIPTION4 should have been changed to ‘Decatur-1’.

When I re-select the data in s1_contract_misc_column_item table, I get these results;
MISC_COLUMN_ITEM_NBR MISC_COLUMN_NBR SORT_ORDER DESCRIPTION

-------------------- --------------- ----------
--------------------------------------------------
                   1               1          7 Ayr
                   2               1          1 Arthur
                   3               1          3 Acton
                   4               1          4 Alma
                   5               1          2 Ariss
                   6               1          5 Arkell
                   7               1          6 Aberfoyle
                   8               2          1 Bayfield
                   9               3          1 Columbia
                  10               4          1 Decatur-1
                  11               5          1 Elmira
                  12               6          1 Flesherton

12 rows selected.

I am confused, also frustrated with working with DYNAMIC SQL, it can be such a PITA!!!!

Any assistance would be appreciated.

Murray Sobol
dbcSMARTsoftware inc. Received on Fri May 21 2010 - 13:13:24 CDT

Original text of this message