Trigger for INSERT to validate input data in SQL Server?-CodePudding

My problem:

Table dbo.student has 'StudentID' like 'SV001'. How can I create trigger to check data insert dbo.student has 'StudentID' begins with "SV" and the numbers in the range 000 to 100.

Example: 'SV099' is valid id to insert, 'SV101' is not valid

CodePudding user response：

Use SQL constraints

CHECK (CAST(SUBSTRING(StudentID,3,LEN(StudentID)) AS int) <= 100)

Example :

create table tb(StudentID varchar(10) CHECK (CAST(SUBSTRING(StudentID,3,LEN(StudentID)) AS int) <= 100));
//test data
insert into tb values('sv000');  //valid
insert into tb values('sv100');  //valid
insert into tb values('sv101');  //invalid

demo in db<>fiddle

Or if you want to use Trigger

Note : you must use the inserted keyword to access the record that has just been added

 Create Trigger TriggerStudentID
    On tb
    AFTER INSERT 
    As
    Begin
        Declare @StudentID varchar(10);
        SET @StudentID = (Select TOP 1 StudentID From inserted);
        If (CAST(SUBSTRING(@StudentID,3,LEN(@StudentID)) AS int) > 100)
           RollBack Transaction
    END

demo in db<>fiddle