Solved

Which loop should I use here and can someone take a look at a trigger for me?

Posted on 2007-11-14
19
342 Views
Last Modified: 2013-12-19
I am having some difficulty with two parts of my project.  
For the PATIENT_CHARGES table, I need to repeat the charge code RMS for all dates until 04/09/2007.  Which type of loop should I use here?

Also, when discharging a patient, I need a trigger that automatically updates the discharge date on the record denoting the current patient location.  Can someone take at my PLOC_DISC_DATE_TRIGGER trigger and let me know if this is correct?

create table PATIENT_DEMOGRAPHIC(

SSNO              NUMBER NOT NULL,

LName            VARCHAR2(25) NOT NULL,     

MI                   VARCHAR2(3),

FName            VARCHAR2(25),

Street               VARCHAR2(35),

City                 VARCHAR2(25),

State                VARCHAR2(2),

Zip                   NUMBER,

DOB                DATE NOT NULL,

Gender            VARCHAR2(1) constraint check_Gender CHECK (Gender IN ('M', 'F')) NOT NULL,

Race                VARCHAR2(3) constraint check_Race CHECK (Race IN ('AFR', 'WHI', 'API', 'HSP', 'OTH')),

constraint PATIENT_DEMOGRAPHIC_PK primary key (SSNO)

);
 

create table PATIENT_ADMISSION(

Patient_ID             NUMBER NOT NULL,

SSNO                    NUMBER NOT NULL,     

Adm_Date            TIMESTAMP NOT NULL,

Disc_Date            DATE,

Adm_Type              VARCHAR2(2) constraint check_Adm_Type CHECK (Adm_Type IN ('IP', 'OP', 'TP', 'RP')),

constraint PATIENT_ADMISSION_PK primary key (Patient_ID),

constraint PATIENT_DEMOGRAPHIC_FK foreign key (SSNO)

references PATIENT_DEMOGRAPHIC (SSNO)

);
 

create sequence SEQ_PATIENT_ADMISSION_PK increment by 1 start with 111111;
 

create or replace trigger PATIENT_ADMISSION_TRIGGER

after insert or update on PATIENT_DEMOGRAPHIC

for each row

begin

      insert into PATIENT_ADMISSION

      (Patient_ID, SSNO, Adm_Date, Disc_Date, Adm_Type)

      values

      (SEQ_PATIENT_ADMISSION_PK.NextVal, :new.SSNO, SYSTIMESTAMP, null, 'OP');

end;

/

show errors trigger PATIENT_ADMISSION_TRIGGER;
 

insert into PATIENT_DEMOGRAPHIC values ('999999999', 'Smith', 'K', 'John', '111 Baker Street', 'Smilesville', 'CA', '90833', '10-JUL-70', 'M', 'WHI');
 

create table PATIENT_ADMISSION_NEW as

select * from PATIENT_ADMISSION;

update PATIENT_ADMISSION_NEW

set Adm_Type = 'IP';

update PATIENT_ADMISSION pa1

set Adm_Type = (

select Adm_Type

from PATIENT_ADMISSION_NEW pa2

where pa1.patient_id = pa2.patient_id);

drop table PATIENT_ADMISSION_NEW;
 

update PATIENT_ADMISSION set Adm_Type = 'IP' where patient_id = 111111;
 

create table INSURANCE_COMPANY(

Comp_Code             VARCHAR2(25) NOT NULL,

Comp_Name            VARCHAR2(25),

Street                     VARCHAR2(25),

City                     VARCHAR2(25),

State                     VARCHAR2(2),

Zip                     NUMBER,

Phone                     NUMBER,

Contact               VARCHAR2(35),

constraint INSURANCE_COMPANY_PK primary key (Comp_Code)

);
 

create table PATIENT_INSURANCE(

Policy_Num            VARCHAR2(20) NOT NULL,

Comp_Code           VARCHAR2(20),

SSNO                    NUMBER NOT NULL,

LName                    VARCHAR2(25),

FName                    VARCHAR2(25),

Eff_Date                  DATE,

Exp_Date               DATE,

Ins_Type               VARCHAR2(3) constraint check_Ins_Type CHECK (Ins_Type IN ('PPO', 'HMO')),

Primary_Phy            VARCHAR2(35),

constraint PATIENT_INSURANCE_PK primary key (Policy_Num, SSNO),

constraint INS_COMP_FK foreign key (Comp_Code)

references INSURANCE_COMPANY (Comp_Code),

constraint PATIENT_SSNO_FK foreign key (SSNO)

references PATIENT_DEMOGRAPHIC (SSNO)

);
 

insert into INSURANCE_COMPANY values ('JUP', 'Jupiter Insurance Inc', '111 Good Health Ave', 'Smilesville', 'MD', '20888', '3019998888', 'Ms. Patty Snyder');

insert into INSURANCE_COMPANY values ('GHI', 'Good Health Insurance Inc', '222 Pink Cheeks Drive', 'Healthville', 'NJ', '08956', '2028936784', 'Mr. Bob Simpson');
 

insert into PATIENT_INSURANCE values ('A0358F6', 'JUP', '999999999',  'Smith', 'John', '01-JAN-07', '31-DEC-07', 'PPO', 'Dr. Joseph Campbell');
 

create or replace trigger PPO_TRIGGER

before insert or update on PATIENT_INSURANCE

for each row

begin

if :new.Ins_Type = 'PPO' and :new.Primary_Phy is null 

then

RAISE_APPLICATION_ERROR (

-20001, 'Primary_Phy cannot be null when Ins_Type is PPO');

end if;

end;

/

show errors trigger PPO_TRIGGER;
 

create table WARD_INFORMATION(

Ward_Code           VARCHAR2(3) constraint check_Ward_Code CHECK (Ward_Code IN ('GWD', 'INC', 'CRD', 'ONC', 'OBG', 'PED')) NOT NULL,

Ward_Name          VARCHAR2(25),

No_Beds                  NUMBER,

Location                  VARCHAR2(25),

constraint WARD_INFORMATION_PK primary key (Ward_Code)

);
 

create table PATIENT_LOCATION(

Patient_ID            NUMBER NOT NULL,

Ward_Code            VARCHAR2(3) NOT NULL,

Bed_Num            NUMBER,

Adm_Date            DATE,

Disc_Date            DATE,

constraint PATIENT_LOCATION_PK primary key (Patient_ID, Ward_Code, Bed_Num, Adm_Date),

constraint PL_PATIENT_ID_FK foreign key (Patient_ID)

references PATIENT_ADMISSION (Patient_ID),

constraint WARD_FK foreign key (Ward_Code)

references WARD_INFORMATION (Ward_Code)

);
 

insert into WARD_INFORMATION values ('CRD', 'Cardiology', '30',  'Building 223');

insert into WARD_INFORMATION values ('ONC', 'Oncology', '20',  'Building 222');
 

insert into PATIENT_LOCATION values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'CRD', '3', '02-APR-07', '09-APR-07');
 

create table RECORDING_PATIENT_CHARGES(

Charge_Code        VARCHAR2(3) NOT NULL,

Description            VARCHAR2(30),

Charge_Category      VARCHAR2(15),

constraint RECORDING_PATIENT_CHARGES_PK primary key (Charge_Code)

);
 

insert into RECORDING_PATIENT_CHARGES values

('RMS', 'Room Stay', 'Facility');
 

insert into RECORDING_PATIENT_CHARGES values

('SUP', 'Room Supplies', 'Facility');
 

insert into RECORDING_PATIENT_CHARGES values

('PRD', 'Prescription Drugs', 'Medicine');
 

insert into RECORDING_PATIENT_CHARGES values

('BLD', 'Blood Tests', 'Clinical Test');
 

insert into RECORDING_PATIENT_CHARGES values

('URI', 'Urine Tests', 'Clinical Test');
 

insert into RECORDING_PATIENT_CHARGES values

('XRA', 'X-Rays', 'Radiology Test');
 

insert into RECORDING_PATIENT_CHARGES values

('EKG', 'Electro Cardiogram', 'Radiology Test');
 

insert into RECORDING_PATIENT_CHARGES values

('MRI', 'Magnetic Resonance Imaging', 'Radiology Test');
 

insert into RECORDING_PATIENT_CHARGES values

('TIS', 'Tissue Tests', 'Pathology');
 

create table PATIENT_CHARGES(

Patient_ID            NUMBER NOT NULL,

Charge_Code          VARCHAR2(3),

Charge_Date           TIMESTAMP,

Charge_Value           NUMBER,

constraint PATIENT_CHARGES_PK primary key (Patient_ID, Charge_Code, Charge_Date),

constraint PC_PATIENT_ID_FK foreign key (Patient_ID)

references PATIENT_ADMISSION (Patient_ID),

constraint CHARGE_CODE_FK foreign key (Charge_Code)

references RECORDING_PATIENT_CHARGES (Charge_Code)

);
 

insert into PATIENT_CHARGES values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'RMS', '02-APR-07', '750');

insert into PATIENT_CHARGES values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'PRD', '04-APR-07', '350');

insert into PATIENT_CHARGES values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'EKG', '08-APR-07', '650');
 

create table PATIENT_ADMISSION_NEW as

select * from PATIENT_ADMISSION;

update PATIENT_ADMISSION_NEW

set Disc_Date = SYSTIMESTAMP;

update PATIENT_ADMISSION pa1

set Disc_Date = (

select Disc_Date

from PATIENT_ADMISSION pa2

where pa1. Disc_Date = pa2. Disc_Date);

drop table PATIENT_ADMISSION_NEW;
 

create or replace trigger PLOC_DISC_DATE_TRIGGER

after update on PATIENT_CHARGES

for each row

begin

      insert into PATIENT_LOCATION

      (Disc_Date)

      values

      (SYSTIMESTAMP);

end;

/

show errors trigger PLOC_DISC_DATE_TRIGGER;
 

create or replace trigger PADM_DISC_DATE_TRIGGER

before insert or update on PATIENT_ADMISSION

for each row

begin

if :new.Disc_Date > :new.Adm_Date 

then

RAISE_APPLICATION_ERROR (

-20002, 'Disc_Date cannot come before Adm_Date');

end if;

end;

/

show errors trigger PADM_DISC_DATE_TRIGGER;
 

create or replace trigger PLOC_DISC_DATE_TRIGGER

before insert or update on PATIENT_LOCATION

for each row

begin

if :new.Disc_Date > :new.Adm_Date 

then

RAISE_APPLICATION_ERROR (

-20003, 'Disc_Date cannot come before Adm_Date');

end if;

end;

/

show errors trigger PLOC_DISC_DATE_TRIGGER;
 
 
 
 

create table ALL_PATIENTS(

PatientSSNO        NUMBER NOT NULL,

PatientLName        VARCHAR2(25) NOT NULL,

PatientMI               VARCHAR2(3),

PatientFName         VARCHAR2(25),

PatientStreet          VARCHAR2(35),

PatientCity              VARCHAR2(25),

PatientState            VARCHAR2(2),

PatientZip               NUMBER,

PatientDOB             DATE NOT NULL,

PatientGender      VARCHAR2(1),

PatientRace      VARCHAR2(3),

PatientAdm_Date      TIMESTAMP NOT NULL,

constraint ALL_PATIENTS_PK primary key (PatientSSNO, PatientAdm_Date)

);
 

create or replace trigger ALL_PATIENTS_TRIGGER

after insert or update on PATIENT_DEMOGRAPHIC

for each row

begin

      insert into ALL_PATIENTS

      (PatientSSNO, PatientLName, PatientMI, PatientFName, PatientStreet, PatientCity, 

PatientState, PatientZip, PatientDOB, PatientGender, PatientRace)

values 

(:new.SSNO, :new.LName, :new.MI, :new.FName, :new.Street, :new.City, :new.State, :new.Zip, :new.DOB, :new.Gender, :new.Race);

end;

/

show errors trigger ALL_PATIENTS_TRIGGER;
 

select * from PATIENT_DEMOGRAPHIC;

select * from PATIENT_ADMISSION;      

select * from INSURANCE_COMPANY;

select * from PATIENT_INSURANCE;

select * from WARD_INFORMATION;

select * from PATIENT_LOCATION;

select * from RECORDING_PATIENT_CHARGES;

select * from PATIENT_CHARGES;
 

insert into ALL_PATIENTS values

('999999999', 'AllLName', 'ALL', 'AllFName', 'AllStreet', 'AllCity', 

'AS', '99999', '05-APR-79', 'F', 'OTH', SYSTIMESTAMP);
 

select * from ALL_PATIENTS;

Open in new window

0
Comment
Question by:CharleneS77
  • 9
  • 8
  • 2
19 Comments
 
LVL 15

Accepted Solution

by:
ishando earned 400 total points
Comment Utility
I would think that  the PLOC_DISC_DATE_TRIGGER would be better off on the PATIENT_ADMISSION, as there is nothing on the PATIENT_CHARGES tables as there is nothing on the charges table to indicate that the patient has been discharged. (sample 1)

For repeating the entry you can do something like sample 2

Sample 1:

  create or replace trigger PLOC_DISC_DATE_TRIGGER

    after update on PATIENT_ADMISSION

    when Disc_Date is not null

    for each row

  begin

    update PATIENT_LOCATION set Disc_Date := SYSTIMESTAMP;

  end;

  /
 
 

Sample 2:

  insert into PATIENT_CHARGES (Charge_Code, Description, Charge_Category)  

  select SEQ_PATIENT_ADMISSION_PK.CurrVal, 'RMS', to_date('02-APR-07','dd-mon-yy') + rownum - 1 

  from (select level from dual 

         connect by level <= (to_date('04-SEP-07','dd-mon-yy') - to_date('02-APR-07','dd-mon-yy')));

Open in new window

0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
Comment Utility
for your RMS update, I wouldn't use a loop at all
just

update patient_charges set charge_code = 'RMS'
where charge_date <= to_date('04/09/2007','mm/dd/yyyy');  


In your  trigger ...
disc_date is a DATE , not a TIMESTAMP, so I'd use SYSDATE
instead of SYSTIMESTAMP.

and you need to insert more data into patient_location
like patient_id so you know who's location you're updating when you insert a new charge.
you need to include some query to determine what the location is too.
that insert simply throws a bunch of nulls into a new row with the current time.


0
 

Author Comment

by:CharleneS77
Comment Utility
>>>>Sample 2:
  insert into PATIENT_CHARGES (Charge_Code, Description, Charge_Category)  
  select SEQ_PATIENT_ADMISSION_PK.CurrVal, 'RMS', to_date('02-APR-07','dd-mon-yy') + rownum - 1
  from (select level from dual
         connect by level <= (to_date('04-SEP-07','dd-mon-yy') - to_date('02-APR-07','dd-mon-yy')));

Did you mean insert into RECORDING_PATIENT_CHARGES?

I get the following error:
  select SEQ_PATIENT_ADMISSION_PK.CurrVal, 'RMS', to_date('02-APR-07','dd-mon-yy') + rownum - 1
         *

ERROR at line 2:
ORA-12899: value too large for column "CM420A05"."RECORDING_PATIENT_CHARGES"."CHARGE_CODE" (actual: 6, maximum: 3)


I also get the following error for the trigger:
when Disc_Date is not null
*

ERROR at line 3:
ORA-04077: WHEN clause cannot be used with table level triggers
0
 

Author Comment

by:CharleneS77
Comment Utility
>>>>and you need to insert more data into patient_location like patient_id so you know who's location you're updating when you insert a new charge.

Like this?

create or replace trigger PATIENT_LOCATION_TRIGGER
after update on PATIENT_ADMISSION
when Disc_Date is not null
for each row
begin
      insert into PATIENT_LOCATION
      (Patient_ID, Ward_Code, Bed_Num, Adm_Date, Disc_Date)
      values
      (SEQ_PATIENT_ADMISSION_PK.CurrVal, :new.Ward_Code, :new.Bed_Num,
      :new.Adm_Date, SYSDATE);

end;
/


>>>>you need to include some query to determine what the location is too.
that insert simply throws a bunch of nulls into a new row with the current time.

I'm not sure I understand where this query would go.
0
 
LVL 15

Expert Comment

by:ishando
Comment Utility
RE patient location trigger: Why are you doing an insert? wouldn't there already be a patient location record, created at time of admission?

>>Did you mean insert into RECORDING_PATIENT_CHARGES?
Guess so - but its not a very good way of doing it. A better approach might be to have another field on the table to record how many times the charge is applied, then have your trigger set value for the  RMS charge (I'm guessing its the room charge) based on the admission and discharge dates
0
 

Author Comment

by:CharleneS77
Comment Utility
>>>>Why are you doing an insert?

ststuber said >>>> and you need to insert more data into patient_location

I must have misunderstood what he meant by insert.  


>>>>have another field on the table to record how many times the charge is applied, then have your trigger set value for the  RMS charge (I'm guessing its the room charge) based on the admission and discharge dates

Yes, RMS is Room Stay charge.

How would I use another field to record the number of times the charge is applied?
0
 

Author Comment

by:CharleneS77
Comment Utility
Also, I get the following error for this trigger:

when Disc_Date is not null
*

ERROR at line 3:
ORA-04077: WHEN clause cannot be used with table level triggers
0
 
LVL 15

Expert Comment

by:ishando
Comment Utility
set the default value to 1, for the room charge (assuming the charge is a daily rate) then the number of charges would probably be discharge date - admission date, then when calculating total charges it would be item charge * number of charges
This is simplistic, eg if there some of the stay is in ICU, which would probably have a higher rate, it might have to be (discharge date - admission date) - #days in ICU
0
 

Author Comment

by:CharleneS77
Comment Utility
OK, I'm confused about the PATIENT_LOCATION trigger.  Can someone clarify this for me?  Do I update or do I insert?

What is the alternative to using the WHEN clause with table level triggers?

RE room charge:  I plan to use what ststuber recommended:
update patient_charges set charge_code = 'RMS'
where charge_date <= to_date('04/09/2007','mm/dd/yyyy');  

Where would I set the default value to 1?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:ishando
Comment Utility
>>OK, I'm confused about the PATIENT_LOCATION trigger.  Can someone clarify this for me?  Do I update or do I insert?
This depends on your data - we are just giving suggestions, you'll need to determine that
In your original code, you are doing an insert, but only setting the disc_date - so it looked like it should have been an update

>>What is the alternative to using the WHEN clause with table level triggers?
You can use an IF or CASE inside your trigger, but WHEN is generally more efficient, as the trigger doesn't fire if its not needed.
0
 

Author Comment

by:CharleneS77
Comment Utility
>>>>but WHEN is generally more efficient, as the trigger doesn't fire if its not needed.

WHEN gives me an error:
ERROR at line 3:
ORA-04077: WHEN clause cannot be used with table level triggers

create or replace trigger PATIENT_LOCATION_TRIGGER
after update on PATIENT_ADMISSION
if Disc_Date is not null
for each row
begin
      update PATIENT_LOCATION
      (Patient_ID, Ward_Code, Bed_Num, Adm_Date, Disc_Date)
      values
      (SEQ_PATIENT_ADMISSION_PK.CurrVal, :new.Ward_Code, :new.Bed_Num,
      :new.Adm_Date, SYSDATE);

end;
/

if Disc_Date is not null
*
ERROR at line 3:
ORA-04079: invalid trigger specification
0
 
LVL 15

Assisted Solution

by:ishando
ishando earned 400 total points
Comment Utility
Note - I don't think you'll be able to use SEQ_PATIENT_ADMISSION_PK.CurrVal here as the sequence is way too likely to have moved on since the patient was admitted...
create or replace trigger PATIENT_LOCATION_TRIGGER

after update on PATIENT_ADMISSION

WHEN (Disc_Date is not null)

for each row

begin

  update PATIENT_LOCATION

  set Disc_Date = :new.disc_date

  where Patient_Id = :new.patient_id;

end;

/ 

Open in new window

0
 
LVL 15

Assisted Solution

by:ishando
ishando earned 400 total points
Comment Utility
oh - i think WHEN should be after FOR EACH ROW
0
 

Author Comment

by:CharleneS77
Comment Utility
I now understand what you and sdstuber were trying to tell me.  I'm still getting one error:

create or replace trigger PATIENT_LOCATION_TRIGGER
after update on PATIENT_ADMISSION
for each row
WHEN (Disc_Date is not null)
begin
  update PATIENT_LOCATION
  set Disc_Date = :new.disc_date
  where Patient_Id = :new.patient_id;
end


WHEN (Disc_Date is not null)
      *

ERROR at line 4:
ORA-04076: invalid NEW or OLD specification
0
 
LVL 15

Assisted Solution

by:ishando
ishando earned 400 total points
Comment Utility
sorry should be

WHEN (new.disc_date is not null)

(note- no ':' used here)
0
 

Author Comment

by:CharleneS77
Comment Utility
OK, I was trying the following:

create or replace trigger PATIENT_LOCATION_TRIGGER
after update on PATIENT_ADMISSION
for each row
begin
                if Disc_Date is not null
                then
      update PATIENT_LOCATION
        set Disc_Date = :new.Disc_Date
        where Patient_Id = :new.Patient_Id;
                end if;
end;
/
show errors trigger PATIENT_LOCATION_TRIGGER;
0
 
LVL 15

Expert Comment

by:ishando
Comment Utility
no doing it that way would be:

begin
  if :new.Disc_Date is not null then
...
0
 

Author Comment

by:CharleneS77
Comment Utility
I'll stick with what you have shown me.  Thank you for your time.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
glad you found your answer.  sorry I wasn't available last night to help clarify my posts.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now