I have a specific requirement as stated below for which i have written a trigger so when user manipulates the column p, q values in table b the corrresponding columns in table a ( x ) should be updated accordingly.

a - b
-- --
x p+q

CREATE OR REPLACE TRIGGER fmlylevel_variables_trg AFTER UPDATE OF SALARYX, SALARYBX, NONFARMX, NONFRMBX, FARMINCX, FRMINCBX, RRRETIRX, RRRETRBX, SOCRRX, INDRETX, JSSDEDX, SSIX, SSIBX
ON B
FOR EACH ROW
BEGIN
PKG_FCI_APP.update_fmly_income_variables;
DBMS_OUTPUT.PUT_LINE('TRIGGER FIRED UPON THE UPDATION OF THE COLUMNS ');
END ;


UPDATE B SET SALARYX = 5000 WHERE FAMID = 110904434407 AND MEMBNO = 2;



SQL Error: ORA-20006: An error was encountered - -4091 -ERROR- ORA-04091: table B is mutating, trigger/function may not see it

Pls let me know .

thanks/kumar

You say you want to update table A, but in the trigger you update table b: UPDATE B ...

within trigger you are precluded from doing any SQL against the table upon which the trigger is based.

As you see the requirement,

when user manipulates the column p, q values in table b the corrresponding columns in table a ( x ) should be updated accordingly.

a - b
-- --
x p+q

So the trigger should be on B table only right ? so that when the user tries to update the columns p, or q then the trigger has to fire and the procudure should update the table table a.x with the modified values of p and q .

The below statement is a part of procedure

UPDATE B SET SALARYX = 5000 WHERE FAMID = 110904434407 AND MEMBNO = 2;


so when the user updates he table B, the trgger fires and updates with column x with the modified values of p and q .


let me know whether my view of understanding is correct or not .

thanks/mike

>let me know whether my view of understanding is correct or not .
ESCHEW OBFUSCATION!

>CREATE OR REPLACE TRIGGER fmlylevel_variables_trg AFTER UPDATE OF SALARYX, SALARYBX, NONFARMX, NONFRMBX, FARMINCX, FRMINCBX, RRRETIRX, RRRETRBX, SOCRRX, INDRETX, JSSDEDX, SSIX, SSIBX
ON B

>when user manipulates the column p, q values in table b

Please post DDL for both tables
I have no idea what columns exist where.

Please post code for PKG_FCI_APP.update_fmly_income_variables;

Simply put, when Oracle throws error(s) your code needs to be changed.

Again my question:
you are telling us you want to update table A but your update statement is against table B.

Could you please use real table names?

If you want to modify columns from the same row from the same table in a trigger, you don't need an UPDATE statement. Simply assign the value to the "new" row:


Again you are only mentioning table B but not table A.
If you want to change table b, then simply do:

new.SALARYX = 5000;

Here is the requirement.. when the user updates any of the columns of MINC the trigger should fire and update the FINC columns

LHS = RHS .

note:-

The tables FINC and MINC are having the columns as stated below .. except that both the tables have FAMID as a column which is primary key


In RHS , The user is updating the values provided in brackets () of MINC table
once updated, the trigger should fire and calculates the summation and updates the columns of FINC. This is my requirement.

note:-

The column FAMID is the key for both the tables.

=========================================
FSALARYX (FINC) = (MINC)sum(SALARYX + SALARYBX)
FNONFRMX (FINC) = (MINC)sum (NONFARMX + NONFRMBX)
FFRMINCX (FINC) = (MINC)sum (FARMINCX + FRMINCBX)
FRRETIRX (FINC) = (MINC)sum (RRRETIRX + RRRETRBX + SOCCRX)
FINDRETX (FINC) = (MINC)sum (INDRETX)
FJSSDEDX (FINC) = (MINC)sum (JSSDEDX)
FSSIX (FINC) = (MINC)sum (SSIX + SSIBX)
=========================================
The trigger i have written is as follows :-

---
CREATE OR REPLACE TRIGGER fmlylevel_variables_trg AFTER UPDATE OF SALARYX, SALARYBX, NONFARMX, NONFRMBX, FARMINCX, FRMINCBX, RRRETIRX, RRRETRBX, SOCRRX, INDRETX, JSSDEDX, SSIX, SSIBX
ON MINC
FOR EACH ROW
DECLARE
-- the right hand side parameters are from user interface
gv_temp_famid number:= :new.famid;
gv_temp_salaryx number:= :new.salaryx;
gv_temp_salarybx number:= :new.salarybx;
gv_temp_nonfarmx number:= :new.nonfarmx;
gv_temp_nonfrmbx number:= :new.nonfrmbx;
gv_temp_farmincx number:= :new.farmincx;
gv_temp_frmincbx number:= :new.frmincbx;
gv_temp_rrretirx number:= :new.rretirx;
gv_temp_rrretrbx number:= :new.rretrbx;
gv_temp_socrrx number:= :new.socrrx;
gv_temp_indretx number:= :new.indretx;
gv_temp_jssdedx number:= :new.jssdedx;
gv_temp_ssix number:= :new.ssix;
gv_temp_ssibx number:= :new.ssibx;

BEGIN
PKG_FCI_APP.update_fmly_income_variables(gv_temp_f amid ,
gv_temp_salaryx ,
gv_temp_salarybx,
gv_temp_nonfarmx,
gv_temp_nonfrmbx,
gv_temp_farmincx,
gv_temp_frmincbx,
gv_temp_rrretirx,
gv_temp_rrretrbx,
gv_temp_socrrx,
gv_temp_indretx,
gv_temp_jssdedx,
gv_temp_ssix ,
gv_temp_ssibx ) ;
DBMS_OUTPUT.PUT_LINE('TRIGGER FIRED UPON THE UPDATION OF THE COLUMNS ');
END ;
----

The procedure is as follows:-
---
create or replace
PACKAGE BODY PKG_FCI_APP AS

function chk_notnull_blank ( colname IN number ) return number is
BEGIN
if ( colname is NOT NULL and colname not in ( -8E14, -7E14, -6E14, -5E14, -4E14, -3E14, -2E14, -1E14, -1E9 )) then
RETURN colname ;
else
RETURN 0;
end if;
END chk_notnull_blank;

procedure update_fmly_income_variables
(gv_temp_famid number,
gv_temp_salaryx number,
gv_temp_salarybx number,
gv_temp_nonfarmx number,
gv_temp_nonfrmbx number,
gv_temp_farmincx number,
gv_temp_frmincbx number,
gv_temp_rrretirx number,
gv_temp_rrretrbx number,
gv_temp_socrrx number,
gv_temp_indretx number,
gv_temp_jssdedx number,
gv_temp_ssix number,
gv_temp_ssibx number) is

cursor c1 is select FAMID, SALARYX, SALARYBX, NONFARMX, NONFRMBX, FARMINCX, FRMINCBX, RRRETIRX, RRRETRBX, SOCRRX, INDRETX, JSSDEDX, SSIX, SSIBX from MINC where famid = gv_temp_famid ;
cursor c2 is select FAMID, FSALARYX, FNONFRMX, FFRMINCX, FRRETIRX, FINDRETX, FJSSDEDX, FSSIX from FINC where famid = gv_temp_famid;
v_flag_boolean boolean := false;
v_famid number := 0 ;
v_temp_sum_fsalaryx number := 0;
v_temp_sum_fnonfrmx number := 0;
v_temp_sum_ffrmincx number := 0;
v_temp_sum_frretirx number := 0;
v_temp_sum_findretx number := 0;
v_temp_sum_fjssdedx number := 0;
v_temp_sum_fssix number := 0;
BEGIN

for i in c2 loop

if ( i.famid = gv_temp_famid ) then
v_flag_boolean := true;
v_famid := gv_temp_famid;
v_temp_sum_fsalaryx := v_temp_sum_fsalaryx + chk_notnull_blank (gv_temp_salaryx) + chk_notnull_blank (gv_temp_salarybx);
v_temp_sum_fnonfrmx := v_temp_sum_fnonfrmx + chk_notnull_blank(gv_temp_nonfarmx)+ chk_notnull_blank (gv_temp_nonfrmbx);
v_temp_sum_ffrmincx := v_temp_sum_ffrmincx + chk_notnull_blank(gv_temp_farmincx) + chk_notnull_blank(gv_temp_frmincbx);
v_temp_sum_frretirx := v_temp_sum_frretirx + chk_notnull_blank (gv_temp_rrretirx) + chk_notnull_blank(gv_temp_rrretrbx) + chk_notnull_blank(gv_temp_socrrx);
v_temp_sum_findretx := v_temp_sum_findretx + chk_notnull_blank(gv_temp_indretx);
v_temp_sum_fjssdedx := v_temp_sum_fjssdedx + chk_notnull_blank(gv_temp_jssdedx);
v_temp_sum_fssix := v_temp_sum_fssix + chk_notnull_blank(gv_temp_ssix ) + chk_notnull_blank(gv_temp_ssibx);

end if ;
end loop ;

update FINC set fsalaryx = v_temp_sum_fsalaryx WHERE famid = v_famid ;
update FINC set fnonfrmx = v_temp_sum_fnonfrmx WHERE famid = v_famid ;
update FINC set ffrmincx = v_temp_sum_ffrmincx WHERE famid = v_famid ;
update FINC set frretirx = v_temp_sum_frretirx WHERE famid = v_famid ;
update FINC set findretx = v_temp_sum_findretx WHERE famid = v_famid ;
update FINC set fjssdedx = v_temp_sum_fjssdedx WHERE famid = v_famid ;
update FINC set fssix = v_temp_sum_fssix WHERE famid = v_famid ;


v_temp_sum_fsalaryx := 0;
v_temp_sum_fnonfrmx := 0;
v_temp_sum_ffrmincx := 0;
v_temp_sum_frretirx := 0;
v_temp_sum_findretx := 0;
v_temp_sum_fjssdedx := 0;
v_temp_sum_fssix := 0;



EXCEPTION
when others then
raise_application_error(-20006,' An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
v_err_code := SQLCODE;
v_err_msg := substr(SQLERRM, 1, 200);
INSERT INTO audit_table (error_number, error_message) VALUES (v_err_code, v_err_msg);

end update_fmly_income_variables ;


---

Hope the code helps ..

Let me know where i am going wrong ..
thanks/mike

Please edit your post and format the SQL using [code] tags.

The way you posted it, it is unreadable.

Hello shammat ,

I have modified the code .

Please review and let me know .

thanks/mike