CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
hoangtd
hoangtd

Reputation: 218

Trigger for INSERT to validate input data in SQL Server?

My problem:

table dbo.student has StudentID like SV001.

How can I create a trigger to check data inserted into dbo.student has a StudentID that begins with SV and the numbers in the range 000 to 100?

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

Upvotes: 0

Views: 2356

Answers (1)

persian-theme
persian-theme

Reputation: 6638

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 a 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.

Or you can use the following trigger

Create Trigger TriggerStudentID
On tb
AFTER INSERT 
As
Begin
   Declare @StudentID varchar(10);
   DECLARE my_Cursor CURSOR FOR SELECT StudentID FROM INSERTED; 
   OPEN my_Cursor; 
        
     
   FETCH NEXT FROM my_Cursor INTO @StudentID;
        
   WHILE @@FETCH_STATUS = 0 
   BEGIN
        
   FETCH NEXT FROM my_Cursor INTO @StudentID;
   If (CAST(SUBSTRING(@StudentID,3,LEN(@StudentID)) AS int) > 100)
       RollBack Transaction
        
   END
   CLOSE my_Cursor; 
   DEALLOCATE my_Cursor;
 END

Demo in db<>fiddle.

Upvotes: 1

Related Questions