INSERT 上的 SQL 触发器，以及 2 个表的 DELETE

Question

我有 2 个名为 Patient 和 Diagnosis 的表格。如下

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

在这里，患者状态可能是[Registered、Diagnosed和OnCourse]

期间，

• 新患者插入，患者状态将为已注册
• 新的诊断插入，患者状态将是已诊断

现在，在诊断删除，我需要检查如果患者在诊断表中至少有一个诊断条目，那么患者状态将是已诊断，否则已注册

那么，如何在 Single Trigger 中完成所有这些条件？

请帮帮我。

Answer 1

根据诊断表上的 INSERT 和 DELETE 触发更新 Patient.Status。

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

我实际上会把它当作两个触发器。一个用于AFTER DELETE，一个用于AFTER INSERT。这意味着 DELETE 代码不会在有 INSERT 时运行，反之亦然。但是上面的代码确实可以正常工作。

编辑

由 Nikola 发现；如果在同一操作中为同一患者插入或更新多个诊断，则这可能会多次更新单个患者记录。这些修改应该解决...

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

还有……

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