How to prevent insert when condition is met-CodePudding

I have a two tables

CREATE TABLE table1 (
    ID int not null,
    Status int not null,
    TimeStamp datetime2(3)

    CONSTRAINT pkId PRIMARY KEY (ID)
);

and

CREATE TABLE Status (
    StatusID int not null,
    StatusName varchar(64) not null
    
    CONSTRAINT pStatus PRIMARY KEY (StatusID)

);

with inserted values

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');

and

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');

I wrote a trigger but it prevent all insert when the StatusID < 4 and what I want is to prevent insert into TimeStamp only when the condition is StatusID < 4. I use 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

Thank you in advance.

CodePudding user response：

With your code you are doing rollback of inserted row when condition is met. If I understood you correctly you just to want override timestamp with null value if StatusId < 4.

If so, you should write that instead of rollback

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

CodePudding user response：

Sounds like you just need a CHECK constraint

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

If you really, really wanted to use triggers for this (not recommended) then it should look like this

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