2

I've 2 tables called Patient and Diagnosis. as follows

Patient        Diagnosis
---------      ------------
ID (PK)        ID (PK) 
Name           PatientID (FK: Reference to Patient => ID)
Status 
******          *****
----------     -----------

Here, Patient Status may be [Registered, Diagnosed and OnCourse]

During,

  • New Patient Insert, Patient Status will be Registered
  • New Diagnosis Insert, Patient Status will be Diagnosed

Now, On Diagnosis Delete, I need to check If Patient has at least one Diagnosis entry in Diagnosis table, then Patient Status will be Diagnosed otherwise Registered

So, How to do all these conditions in Single Trigger?

Please help me.

Improve this question
1
  • It may just be me, but I'm not clear on what you are asking. Please could you list (as an edit to your question) what you want the trigger(s) to do? [I think you want certainly want 3) and maybe 2) but also possibly 1). It's just not very clear from your question.] 1). When a new Patient record is inserted, force the Status to be Registered? 2). When a new Diagnosis is inserted, for the Status to be Diagnosed? 3). When the last Diagnosed record is deleted, force the Status to be Registered? Commented May 17, 2012 at 8:54

2 Answers 2

Reset to default
5

Trigger to update Patient.Status based on INSERTs and DELETEs on the Diagnosis Table.

CREATE TRIGGER dbo.Diagnosis_TrgInsDel
  ON  dbo.Diagnosis
  AFTER DELETE, INSERT
AS 
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


    -- Change the Status to 'Registered' after the last
    -- Diagnosis record is deleted
    --------------------------------------------------------------------------------
    UPDATE
      Patient
    SET
      [Status] = 'Registered'
    FROM
      Patient
    INNER JOIN
      Deleted
        ON Deleted.PatientID = Patient.ID
    WHERE
      NOT EXISTS (SELECT * FROM Diagnosis WHERE PatientID = Deleted.PatientID)


    -- Change the Status to 'Diagnosed' after an Insert and the
    -- current Status is 'Registered'
    --------------------------------------------------------------------------------
    UPDATE
      Patient
    SET
      [Status] = 'Diagnosed'
    FROM
      Patient
    INNER JOIN
      Inserted
        ON Inserted.PatientID = Patient.ID
    WHERE
      Patient.[Status] = 'Registered'

END

I would actually have don this as two triggers. One for AFTER DELETE and one for AFTER INSERT. That would mean that the DELETE code wouldn't run when there is an INSERT and vice versa. But the above code will indeed work correctly.

EDIT

AS spotted by Nikola; if multiple diagnosise are inserted or updated, in the same operation, for the same patient, then this will potentially update a single patient record multiple times. These modifications should address that...

    UPDATE
      Patient
    SET
      [Status] = 'Registered'
    WHERE
      NOT EXISTS (SELECT * FROM Diagnosis WHERE PatientID = Patient.ID)
      AND EXISTS (SELECT * FROM Deleted   WHERE PatientID = Patient.ID)

And...

    UPDATE
      Patient
    SET
      [Status] = 'Diagnosed'
    WHERE
          Patient.[Status] = 'Registered'
      AND EXISTS (SELECT * FROM Inserted WHERE PatientID = Patient.ID)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

For the first part, you may simply add default constraint:

alter table patient add constraint df_status default 'Registered' for status

If this would not be enough because your front end is incapable of omitting status in insert or setting value to DEFAULT, you can create a trigger:

create trigger PatientInsertTrigger on patient
after insert
as
   -- trigger should not alter @@rowcount
   set nocount on

   update patient
      set status = 'Registered'
     from Patient
  -- Inserted is a pseudotable holding newly inserted rows
  -- This is how we know what records to update
    inner join Inserted
       on Patient.ID = Inserted.ID

When records are added to or removed from diagnosis, patient status should be updated according to number of matching records in diagnosis. Luckily, at the time trigger is invoked records are already in table, so it is sufficient to take count() in both cases.

create trigger DiagnosisTrigger on diagnosis
after insert, delete
as
   set nocount on

   update patient
      set status = case when d.NumberOfDiagnosis <> 0 
                        then 'Diagnosed'
                        else 'Registered'
                    end
     from Patient
    inner join
    (
      select PatientID, count(*) NumberOfDiagnosis
        from Diagnosis
       -- Get all patients with added or removed diagnosis
       where PatientID in (select PatientID 
                             from Inserted 
                            union 
                           select PatientID 
                           -- Pseudotable holding removed records
                             from Deleted)
       group by PatientID
    ) d
      on Patient.ID = d.PatientID

Status should be StatusID, but you did not mention appropriate id numbers.

Improve this answer

4 Comments

Although not mistakes this does have a slight inefficiency - It will update the status even if it's not required. (On the second insert you change the status from 'Diagnosed' to 'Diagnosed'. The same after a delete where there is still another Diagnosis record.) Also, by using COUNT(*) instead of some use of EXISTS the performance will degrade as the number of diagnosise increases. [These are likely to be only minor in this particular case, but they're worth noting for the OP's learning purposes, and really are measurably relevant in large scale databases.]
@Dems Thank you for the insight. While I was trying to keep it simple you did it simple. I have a question though - in case of insert into ... select your code will possibly update the same patient multiple times with same value; and, is it really efficient to filter out updates to the same value? For instance, is status <> 'x' an improvement in update patient set status = 'x' where patientid = 123 and status <> 'x' or a clutter? I know that this is one record only, but I had some ranking updates and was uncertain whether to filter or not updates to same value.
You're right, if the same patient has multiple diagnoise inserted/delete then my answer does update the same patient record multiple times. My mistake, and good spot! As for the status <> 'x' then this is an improvement. The record is being read into memory anyway, and this cpu overhead is low; much, much lower then un-necessary writes when setting the status un-necessarily. (Though one could rightly argue that that depends on how often the un-necessary writes actually occure.)
@Dems thank you for the clarification. I've often wandered about it but was unable to come to conclusion. Thanks again :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.