Help with a trigger

[jayjabour]

I have a table called patient with four fields called office1, office2, office3 and office4. There is a job that runs at not that puts the value 'BAL' in one of those fields. Sometimes it puts the value in office1 sometimes in office2. My point is it is not always in the same field. It used to always put it in office4. This ment a patient had a balance on their account. The cashiers wanted me to write a trigger that if they paid there balance it removed the 'BAL' value from patient.office4. So I wrote that trigger and it works.:

CREATE OR REPLACE TRIGGER AXIUM."RMVBALTRG" 
BEFORE UPDATE OF
"LastPaymentAmount"
ON "PATIENT"
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
v_bal NUMBER(10);
BEGIN
update  PTBAL_TMP set "Sum" = (Select sum("Sum" - :NEW."LastPaymentAmount" ) from ptbal_tmp where "Patient" = :NEW."Patient") where "Patient"= :NEW."Patient";
select "Sum" into v_bal from PTBAL_TMP where "Patient"=:NEW."Patient" ;
IF (v_bal <=0) THEN
:NEW."Office4" := ' ';
END IF;
exception
  when others then
    null;
END;
/

this only works when the 'BAL' value is in the office4 field. Now I need to re-write the trigger so if 'BAL is in office1 it removes the 'BAL' value from office1, but if the 'BAL' value is in office2 it removes it from there, and so on and so on. So my first attempt was this:

CREATE OR REPLACE TRIGGER AXIUM."RMVBALTRG" 
BEFORE UPDATE OF
"LastPaymentAmount"
ON "PATIENT"
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
v_bal NUMBER(10);
BEGIN
update  PTBAL_TMP set "Sum" = (Select sum("Sum" - :NEW."LastPaymentAmount" ) from ptbal_tmp where "Patient" = :NEW."Patient") where "Patient"= :NEW."Patient";
select "Sum" into v_bal from PTBAL_TMP where "Patient"=:NEW."Patient" ;
IF (v_bal <=0) THEN
If "Office1" = 'BAL' THEN
:NEW."Office1" := ' ';
elsif "Office2" = 'BAL' THEN
:NEW."Office2" := ' ';
ELSIF "Office3" = 'BAL' THEN
:NEW."Office3" := ' ';
END IF;
END IF;
exception
  when others then
    null;
END;
/

but the trigger does not compile it gives me this error:
identifier 'office1' must be declared.
I tried adding the table to the code like this If "PATIENT"."Office1" = 'BAL' THEN
but then it errors with table, view or sequence reference 'PATIENT.Office1' not allowed in this context. Am I even approaching this change correctly or am I way off in how I need to re-write this trigger. Any help would be greatly appreciated.

[jayjabour]

I tried this

CREATE OR REPLACE TRIGGER AXIUM."RMVBALTRG" 
BEFORE UPDATE OF
"LastPaymentAmount"
ON "PATIENT"
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
v_bal NUMBER(10);
BEGIN
update  PTBAL_TMP set "Sum" = (Select sum("Sum" - :NEW."LastPaymentAmount" ) from ptbal_tmp where "Patient" = :NEW."Patient") where "Patient"= :NEW."Patient";
select "Sum" into v_bal from PTBAL_TMP where "Patient"=:NEW."Patient" ;
IF (v_bal <=0) THEN
update patient set  "Office1"=DECODE(trim("Office1"),'BAL',' ',"Office1"), 
                            "Office2"=DECODE(trim("Office2"),'BAL',' ',"Office2"), 
                            "Office3"=DECODE(trim("Office3"),'BAL',' ',"Office3"),
                            "Office4"=DECODE(trim("Office4"),'BAL',' ',"Office4"), 
                            "Office5"=DECODE(trim("Office5"),'BAL',' ',"Office5") where "Patient"=:NEW."Patient";
END IF;
exception
  when others then
    null;
END;
/

it compiles fine but doesn't do anything when I update the patient table.

[PAVB]

It looks like that what drives the process is actually the value of "v_bal" and not the "BAL" value on officeN columns then... if "v_bal" <= 0 then just set either NULL or whatever value is needed on the four OfficeN columns for the affected row.

[jayjabour]

Thanks for the reply. I am not sure if this is what you mean but this is what I went with and it seems to be doing the trick..

CREATE OR REPLACE TRIGGER AXIUM."RMVBALTRG" 
BEFORE UPDATE OF
"LastPaymentAmount"
ON "PATIENT"
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
v_bal NUMBER(10);
BEGIN
update  PTBAL_TMP set "Sum" = (Select sum("Sum" - :NEW."LastPaymentAmount" ) from ptbal_tmp where "Patient" = :NEW."Patient") where "Patient"= :NEW."Patient";
select "Sum" into v_bal from PTBAL_TMP where "Patient"=:NEW."Patient" ;
IF (v_bal <=0) and :NEW."Office1"='BAL' THEN
:New."Office1" := ' ';
elsif (v_bal <=0) and :NEW."Office2"='BAL' THEN
:New."Office2" := ' ';
elsif (v_bal <=0) and :NEW."Office3"='BAL' THEN
:New."Office3" := ' ';
elsif (v_bal <=0) and :NEW."Office4"='BAL' THEN
:New."Office4" := ' ';
elsif (v_bal <=0) and :NEW."Office5"='BAL' THEN
:New."Office5" := ' ';
END IF;
exception
  when others then
    null;
END;
/

[PAVB]

Good job.
Are you certain that a particular patient can have no more of one OfficeN column with BAL value? If this is the case you soluton works like a charm.