在插入值触发之前检查

Question

我有一个表如下：

CREATE TABLE [dbo].[Vaccination]
(
    [MedicalID] [int] NOT NULL PRIMARY KEY,
    [VaccineName] [varchar](20) NOT NULL,
    [VaccineDate] [date] NOT NULL,
    [DoseNo] [int] NOT NULL PRIMARY KEY,
    [FacilityName] [varchar](20) NOT NULL,
)

因此，我有一个约束，只能在表中允许2个值：剂量1和剂量2

ALTER TABLE [dbo].[Vaccination] 
    ADD CONSTRAINT No_of_Doses CHECK ([DoseNo] = 1 OR [DoseNo] = 2)

我想创建一个触发器，而仅在1st确实存在的第二剂量的值仅在第二剂量的情况信息。同样，要插入的第一剂毫无问题。

这是我的尝试：

CREATE TRIGGER Dose1Check
ON [dbo].[Vaccination]
BEFORE INSERT
BEGIN
    INSERT INTO [dbo].[Vaccination]
        SELECT 22, 'Moderna', '2022-04-22', 1, 'CVS'
        WHERE IF EXISTS (SELECT 1
                         FROM [dbo].[Vaccination]
                         WHERE [MedicalID] = 22 AND [DoseNo] = 1)
            SELECT * 
            FROM [dbo].[Vaccination];
END

上述触发器的代码的以下部分（独立执行）不允许我插入第一剂量。

 INSERT INTO [dbo].[Vaccination]
     SELECT 22, 'Moderna', '2022-04-22', 1, 'CVS'
     WHERE 
         IF EXISTS (SELECT 1
                    FROM [dbo].[Vaccination]
                    WHERE [MedicalID] = 22 AND [DoseNo] = 1)
             SELECT * FROM [dbo].[Vaccination];

这是表的一些值

MedicalID,  VaccineName,    VaccineDate,    DoseNo, FacilityName
14, Moderna,    2022-04-22, 1,  Daisy Family Health
14, Moderna,    2022-04-26, 2,  Daisy Family health
15, Moderna,    2022-04-28, 1,  Daisy Family Health
15, Moderna,    2022-04-22, 2,  Daisy Family Health
16, Pfizer, BioNTech,   2022-04-28  1   Daisy Family Health

Answer 1

首先，您的示例数据暗示该表具有复合主键（MedicalId，doseno）。 Thus

CREATE TABLE [dbo].[Vaccination](
    [MedicalID] [int] NOT NULL ,
    [VaccineName] [varchar](20) NOT NULL,
    [VaccineDate] [date] NOT NULL,
    [DoseNo] [int] NOT NULL CHECK (DoseNo IN (1,2)),
    [FacilityName] [varchar](20) NOT NULL,
    Primary Key ([MedicalID],[DoseNo])
)

You can check if inserted data contain out of order doseNo with a trigger

create trigger Dose1Check
  on [dbo].[Vaccination]
  after insert as
begin
  if exists (
    select 1
    from ( -- check single dose insertions only
          select [MedicalID], max(DoseNo) DoseNo
          from inserted
          group by [MedicalID]
          having count(*) = 1
    ) i
    left join [dbo].[Vaccination] v on i.[MedicalID] = v.[MedicalID] and v.DoseNo = 1
    where v.[MedicalID] is null and i.DoseNo = 2 )
  begin
      RAISERROR ('Bad DoseNo' , 16, 1)
      ROLLBACK TRANSACTION;
  end
end

db<>fiddle< /a>