Problems with a Trigger
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