How can I add a trigger to check if a value is within a range before inserting?-CodePudding

Table example : Table T1( ID int primary key,

n1 int )

How can I add a trigger to check if n1 is between 0 and 10 before inserting?

CodePudding user response：

Answer by @500-InternalServerError (will delete if they post):

There is no need for a trigger. Use a CHECK constraint instead.

CREATE TABLE Persons (
  ID int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Age int CHECK (Age>=18)
);

db<>fiddle

CodePudding user response：

In this trigger, if one of inserted data (n1) is between 0 and 10, "ROLLBACK" cancel inserting.

CREATE TRIGGER dbo.trigger1
ON dbo.table1
for INSERT
AS BEGIN
    IF EXISTS(
        SELECT 1
        FROM INSERTED ins WHERE ins.n1 BETWEEN 0 AND 10
    ) BEGIN
        ROLLBACK;
    END
   END
GO