title: SQL trigger on INSERT, and DELETE with 2 Tables
body: <p>I've 2 tables called Patient and Diagnosis. as follows</p>
  
  <pre>
  Patient        Diagnosis
  ---------      ------------
  ID (PK)        ID (PK) 
  Name           PatientID (FK: Reference to Patient => ID)
  Status 
  ******          *****
  ----------     -----------
  </pre>
  
  <p>Here, Patient Status may be [<strong>Registered</strong>, <strong>Diagnosed</strong> and <strong>OnCourse</strong>]</p>
  
  <p>During, </p>
  
  <ul>
  <li>New Patient Insert, Patient Status will be <strong>Registered</strong> </li>
  <li>New Diagnosis Insert, Patient Status will be <strong>Diagnosed</strong></li>
  </ul>
  
  <p>Now, On
  <strong>Diagnosis Delete</strong>, I need to check If Patient has at least one Diagnosis entry in <strong>Diagnosis table</strong>, then Patient Status will be <strong>Diagnosed</strong> otherwise <strong>Registered</strong></p>
  
  <p>So, How to do all these conditions in Single Trigger?</p>
  
  <p>Please help me.</p>