Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    42

    Unanswered: Trigger help please :D.

    Good morning,

    I have the following table:

    CREATE TABLE STAY
    (
    Patient_id varchar2(20) NOT NULL,
    Bed_no varchar2(20) NOT NULL,
    Ward_name varchar2(30) NOT NULL,
    Centre_name varchar2(50) NOT NULL,
    Admission_date varchar2(20) NOT NULL,
    CONSTRAINT STAY_PK
    PRIMARY KEY (Patient_id,Bed_no,Ward_name,Centre_name)
    );


    What i need to do is create a trigger to make sure that a patient is not placed in a bed that is already taken. I have assumed that each hostpital has each bed numbered. Not by department then bed number. This hopefully makes it a little easier. Im pretty sure that i cannot compare 3 different columns like i have. I could create 2 tirggers one after another but this is would take up unecessary use of the server etc. Any change of some pointers.

    create or replace trigger bed_verify
    before update of BED_NO, CENTRE_NAME or insert
    on stay
    for each row
    begin

    if :new.BED_NO is = BED_NO and :new.CENTRE_NAME = CENTRE_NAME and :new.ADMISSION_DATE = ADMISSION_DATE then
    raise_application_error(-20001, 'This bed is already taken');
    end if;

    end;


    Thanks

  2. #2
    Join Date
    Oct 2003
    Posts
    87
    Your problem might be even more difficult. Would it be possible that two or more people working the same Ward and Centre might be trying to assign the same bed unknowingly? The beds in a Ward/Center are a finite resource and you would have to know the number of beds available per Ward/Centre; then serialize on that resource while obtaining a bed number for a patient.
    Oracle - DB2 - MS Access -

  3. #3
    Join Date
    Mar 2004
    Posts
    42
    That would be the case. The question doesn't really hint at that sort of opertation. I think they are just trying to get us to implement triggers to validate data and aren't really concered with the locking of tables etc.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If it is just a case of checking no record already exists for the same bed and admission date, then a unique constraint would be the way to do it, not a trigger. However, I would have thought the check would be more complex: someone may have been admitted to that bed last week, but is still there. So shouldn't there be another date e.g. discharge_date, in which case you would need to check for overlapping date ranges?

  5. #5
    Join Date
    Mar 2004
    Posts
    42
    You are right. The assignment does say that not all the questions require action, perhaps this is one of them. The DDL and ERD was given to us to work on. I know its not the most realistic peice of work.

    Ill take a look at it and get back to the forum if i have any specific questions.

    Thanks for the pointers.

    Jon
    Last edited by jonrohan; 05-20-04 at 13:48.

Posting Permissions

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