Results 1 to 5 of 5

Thread: Trigger help!

  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Trigger help!

    Hello,

    I am completely new to SQL and am trying to create a trigger to update a field when a field in another table is changed...

    I have the following two tables with these fields in them...

    PATIENTS
    Patient_ID
    Date_Of_Last_Appointment (Date)

    APPOINTMENTS
    Appointment_ID
    Patient_ID
    Date_Of_Appointment (Date)
    Status

    When a patient attends their appointment, the Status field is changed to "Arrived". When this happens I want the date of the appointment to be inserted into the "Date_Of_Last_Appointment" field for the appropriate patient in the Patients table.

    I would really appreciate some help in writing this. I am afraid I haven't got anything at the moment as I have no clue where to even begin. Usually I try to write something first to get help on so sorry!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its redundant data
    you already know the date of the patients previous appointment.
    just run a query that pulls the last appointment BEFORE you set the status to arrived.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2012
    Posts
    2
    Oh ok. Thanks!

    But then I also wanted to be able to calculate when the patient is next due to be seen depending on this date.

    The PATIENTS table also has the fields Scheme and Next_Appointment_Due.

    The scheme can be 1,2,3,6,9 or 12 months and I wanted to automatically add this to the Date_Of_Last_Appointment field to automatically calculate the Next_Appointment_Due.

    Any ideas on how I might go about doing that?

    Thanks.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the information already exists, its readily accessible
    ..presumably you look for the most recent appointment for that patient which as a status of whatever.
    unless there is a good reason for duplicating the information then the current storage is good enough.
    duplicating the data in this case serves no worthwhile purpose in myu books, but then again I'm not aware of what you are trying to achieve so there may be a good enough reason to do it.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Look at the DATE_ADD or DATEADD function. This allows you to add an interval to a current date. For example:

    Code:
    mysql> select now(), date_add(now(), INTERVAL 1 DAY) 1day, DATE_ADD(now(), INTERVAL 1 MONTH) 1month, date_add(now(), INTERVAL 3 MONTH) 3month;
    +---------------------+---------------------+---------------------+---------------------+
    | now()               | 1day                | 1month              | 3month              |
    +---------------------+---------------------+---------------------+---------------------+
    | 2012-04-23 19:10:30 | 2012-04-24 19:10:30 | 2012-05-23 19:10:30 | 2012-07-23 19:10:30 | 
    +---------------------+---------------------+---------------------+---------------------+
    1 row in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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