Help with a trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Help with a trigger

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    Help with a trigger

    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.:
    Code:
    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:
    Code:
    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.

  2. #2
    Join Date
    Mar 2005
    Posts
    143
    I tried this
    Code:
    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.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Mar 2005
    Posts
    143
    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..
    Code:
    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;
    /

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width