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
Advertisement
Answer
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.