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