如何在满足条件时阻止插入

Question

我有一个两张表的

CREATE TABLE table1 (
ID int not null,
Status int not null,
TimeStamp datetime2(3)
CONSTRAINT pkId PRIMARY KEY (ID)
);

和

CREATE TABLE Status (
StatusID int not null,
StatusName varchar(64) not null

CONSTRAINT pStatus PRIMARY KEY (StatusID)
);

插入值

INSERT INTO table1 (ID,StatusID,TimeStamp) VALUES 
(1,1,'2021-06-15 07:30:31'),
(2,2,'2021-07-15 07:30:31'),
(3,3,'2021-08-15 07:30:31'),
(4,4,'2021-09-15 08:30:31'),
(5,5,'2021-09-15 07:30:31'),
(6,5,'2021-09-15 07:30:31'),
(7,4,'2021-09-15 07:30:31'),
(8,2,'2021-09-15 07:30:31'),
(9,1,'2021-09-15 07:30:31');

和

INSERT INTO dbo.Status (StatusID,StatusName) VALUES
(1,'wants to enroll'),
(2,'granted enrollment'),
(3,'declined enrollment'),
(4,'attending the course'),
(5,'finished the course');

我写了一个触发器，但当StatusID<4，并且我想要的是仅当条件为StatusID<4.我使用SQL Server。

CREATE TRIGGER dbo.Prevent_Insert4
ON dbo.table1
FOR INSERT
AS
SET NOCOUNT ON
IF  EXISTS(SELECT * 
FROM dbo.table1 t 
JOIN inserted i ON t.ID=i.ID 
WHERE t.StatusID<4)
BEGIN
RAISERROR('You can not update when StatusId value < 4', 16, 1)
ROLLBACK TRAN
SET NOCOUNT OFF
RETURN
END
SET NOCOUNT OFF

提前谢谢。

Answer 1

听起来您只需要一个CHECK约束

ALTER TABLE table1
ADD CONSTRAINT CHK_Status
CHECK(Status >= 4 OR TimeStamp IS NULL);

---

如果你真的，真的想使用触发器(不推荐(，那么它应该看起来像这个

CREATE OR ALTER TRIGGER dbo.Prevent_Insert4
ON dbo.table1
FOR INSERT, UPDATE
AS
SET NOCOUNT ON;
IF  EXISTS(SELECT 1
FROM inserted i
WHERE i.StatusID < 4
AND i.TimeStamp IS NOT NULL)
BEGIN
THROW 50001, 'You can not set TimeStamp when StatusId value < 4', 1;
-- no need for rollback, will happen automatically if you use THROW
END;
go

Answer 2

在满足条件时，使用代码回滚插入的行。如果我正确理解你，你只想用null值覆盖时间戳，如果StatusId<4.

如果是这样的话，你应该写它，而不是回滚

IF EXISTS(SELECT * 
FROM dbo.table1 t 
JOIN inserted i ON t.ID=i.ID 
WHERE t.StatusID<4)
BEGIN
UPDATE t SET t.TimeStamp = NULL 
FROM inserted i
JOIN dbo.table1 t ON i.ID = t.ID
WHERE t.StatusID<4
END