Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2007-11-14
19
345 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
ID: 20286290
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 74

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
ID: 20286295
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
ID: 20295418
>>>>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
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.

 

Author Comment

by:CharleneS77
ID: 20295598
>>>>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
ID: 20295662
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
ID: 20295707
>>>>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
ID: 20295715
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
ID: 20295725
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
ID: 20296009
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
 
LVL 15

Expert Comment

by:ishando
ID: 20296098
>>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
ID: 20296140
>>>>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
ID: 20296160
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
ID: 20296186
oh - i think WHEN should be after FOR EACH ROW
0
 

Author Comment

by:CharleneS77
ID: 20296297
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
ID: 20296312
sorry should be

WHEN (new.disc_date is not null)

(note- no ':' used here)
0
 

Author Comment

by:CharleneS77
ID: 20296346
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
ID: 20296355
no doing it that way would be:

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

Author Comment

by:CharleneS77
ID: 20296387
I'll stick with what you have shown me.  Thank you for your time.
0
 
LVL 74

Expert Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

829 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