trigger - updating from another table

[anniez]

You've helped me before with a similar situation but I just can't get this one working.
When a clinic_number gets changed in table_A, the clinic_name also needs to get updated (unless the clinic_number gets changed to '000'.) Table_B contains clin_num and clin_name. I've tried many variations and they just don't work. Can you help me again?
Thanks.

CREATE OR REPLACE TRIGGER TR_CLINIC_NAME
AFTER UPDATE OF CLINIC_NUMBER ON PATIENT_DATA
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW

BEGIN
IF :NEW.CLINIC_NUMBER <> '000'
THEN
:NEW.WARD := (SELECT CLIN_NAME FROM CLINIC_DATA
WHERE
:NEW.CLINIC_NUMBER := CLINIC_DATA.CLIN_NUM)
END IF;
END;

 

[SantaMufasa]

Annie,

First, let's get your trigger working, then I'll share some additional thoughts with you.

There are two issues preventing your trigger from working:
1) You cannot update a ":new." column unless your trigger is a "BEFORE" trigger
2) You cannot "SELECT" in PL/SQL unless it is "...INTO..." a receiving field or unless the SELECT is part of a CURSOR definition.

Therefore, your corrected code appears here (along with proof-of-concept DML statements):

CREATE OR REPLACE TRIGGER TR_CLINIC_NAME
BEFORE UPDATE OF CLINIC_NUMBER ON PATIENT_DATA
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW

BEGIN
IF :NEW.CLINIC_NUMBER <> '000' THEN
 SELECT CLIN_NAME into :NEW.WARD
   FROM CLINIC_DATA
  WHERE CLINIC_DATA.CLIN_NUM = :NEW.CLINIC_NUMBER;
END IF;
END;
/

Trigger created.

col a heading "ID" format 9
col b heading "NAME" format a10
col c heading "CLINIC|NUMBER" format 99
col d heading "WARD" format a15
select id a, name b, clinic_number c, ward d
from PATIENT_DATA;

              CLINIC
ID NAME       NUMBER WARD
-- ---------- ------ --------------
 1 Dave Hunt      10 Sandy

update patient_data set clinic_number = 20 where id = 1;

1 row updated.

select id a, name b, clinic_number c, ward d
from PATIENT_DATA;

              CLINIC
ID NAME       NUMBER WARD
-- ---------- ------ ---------------
 1 Dave Hunt      20 Salt Lake City

1 row selected.

So, we got the trigger working properly. But here are my follow-on concerns:

1) You are disobeying a fundamental rule of relational database design (Second Normal Form) when you include the name of the Ward (Clinic Name) in the PATIENT table. The only thing about the CLINIC that should appear in the PATIENT table is the CLINIC_NUMBER as a Foreign Key to the Primary Key (CLIN_NUM) of the CLINIC table. Then, when you need the name of the WARD (CLINIC_NAME), you do a join between the two tables.

2) When you change the CLINIC_NUMBER to "000", the WARD name remains the same as before. You should either have a CLINIC whose number is "000" and the name is "Unassigned", or you should cause the CLINIC_NUMBER to become NULL if, in fact, "000" means "Unassigned". (Latter solution is better.)

3) Your trigger should have an EXCEPTION section to handle cases where there is no matching CLIN_NUM in the CLINIC table.

Let me know your thoughts,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:52 (30Nov04) UTC (aka "GMT" and "Zulu"),
@ 10:52 (30Nov04) Mountain Time

 

[anniez]

Thanks - I can't believe how easy you make it seem.

Thanks also for the advise. However, one clinic could have several codes (sections).
ex. Noyes Clinic could be 551, 552, 651 or 652. Staff generating reports are apparently not capable of relating tables so I was told to enter the clinic name.

2. '000' indicates they are no longer associated with a clinic. Null would mean they were never in a clinic (In-patient).

3. At this point, the clinic numbers have to be in this table but it could possibly change and an exception routine would be pro-active.

At Habitat for Humanity, it's more efficient to build a house from scratch than to refurbish one - same with inherited programs.

Thanks so much for your help

 

[SantaMufasa]

Annie,

I understand. As an alternative, however, if you have the opportunity to effect a minor design change, you could build a VIEW to provide the "Staff generating reports" with their access to the data in the de-normalised form that they need, but the VIEW would actually gather information from the normalised tables underneath the VIEW.

The reason I'm suggesting this becomes clear under this scenario:

15,000 patients have "Salt Lake City" as their clinic name. That same clinic becomes "Salt Lake City South" as a result of new construction. That name change in your denormalised tables requires an avoidable update to 15,000 records, while if you stored just the CLINIC_NUMBER in the PATIENT records, there would be just a one-row update in the CLINIC_DATA table.

I believe you could achieve the best of both worlds with minimum adjustment to both your tables and application(s).

Good luck,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:47 (30Nov04) UTC (aka "GMT" and "Zulu"),
@ 12:47 (30Nov04) Mountain Time

 

[anniez]

I totally agree with you.

May I ask another related question here or shall I start another thread? The trigger works great but it isn't what I needed.

When a patient is admitted here, our local patient table gets updated. Among other fields, status is 1 for inpatient, 2 for clinic; clinic_num (if it's a clinic), Ward (if inpatient); site (name of clinic or unit). My code works great for readmissions but if it must insert a new record, it works except for getting the clinic name from that other table. Am I making sense?
Here's the code for readmissions:
SET
SITE = (SELECT UNIT_NAME FROM V_WARD_CLIN WHERE :NEW.ADM_UNIT = V_WARD_CLIN.UNIT_NUMBER)
....
-- but if it inserts a new record
INSERT INTO PATIENT_DATA(PT_ID, WARD, CLINIC_NUM, SITE, (this would be the clinic name), .....)
VALUES NEW.PT_ID, :NEW.ADM_UNIT,
DECODE(SUBSTRNEW.ADM_TYPE,1,1),2,:NEW.ADM_UNIT,
(SELECT UNIT_NAME FROM V_WARD_CLIN WHERE :NEW.ADM_UNIT = V_WARD_CLIN.UNIT_NUMBER),......
This line has never worked so the clinic name has never been entered for new admissions.
Can you help with the syntax?