In this tutorial i’ll create SQL trigger examples.
This examples will help you to learn how to write triggers.
If you didn’t download the database you can download from here.
Examples
Example 1: Trigger prevents the entry of data to student table
Example 2: Trigger to prevent student addition to class 10A.
Example 3: The trigger that prevents students from updating their classes and genders at the same time.
Example 4: Trigger that prevents the addition of type names with less than six characters.
Example 5: The trigger prevents to delete more than one records on borrows table at one time.
Example 6: Trigger that prevents updating the student names.
Example 7: The trigger, adds points to students that up to the points of the book.
Example 8: The trigger prevents to delete the student. It updates the student’s points to zero
Example 9: The trigger prevents to take book(type drama) of students which class level nine
Example 10: The trigger lets:The length of student names must be between 5 and 10 characters.
Example 11: The trigger prevents to lend books that has not been brought yet.
Example 12: When a student add to student table,add a borrow record with randomly selected book.
Example 13: The student’s birthdate who will be added to the students table must be bigger than 2003
Example 14:A student can get only one book in the same day.
Example 15:Increase the point of each borrowed book by 1 point
Solutions
Example 1: Trigger prevents the entry of data to student table.
Solution 1
|
1 2 3 4 5 6 7 8 9 |
Create Trigger Tr_NotAllowedAddingStudents on Students Instead of Insert As Begin raiserror('Adding students canceled',2,3); End |
Solution 2
|
1 2 3 4 5 6 7 8 9 10 |
Create Trigger Tr_NotAllowedAddingStudents on Students After Insert As Begin Rollback Transaction raiserror('Adding students canceled',2,3); End |
Example 2: Trigger to prevent student addition to class 11C.
Solution
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Create Trigger Tr_NotAllowedAddingStudentsto10AClass on Students After Insert As Begin If (exists(select * from inserted where class ='11C')) Begin Rollback Transaction raiserror('Adding students to 10A Class canceled',2,3); End End End |
Example 3: The trigger that prevents students from updating their classes and genders at the same time.
Solution
|
1 2 3 4 5 6 7 8 9 10 |
Create Trigger triggerName on students for update as Begin If(update(gender) and update(class)) Begin raiserror('You can write error message here',7,2) rollback transaction End End |
Example 4: Trigger that prevents the addition of type names with less than seven characters.
Solution
|
1 2 3 4 5 6 7 8 9 |
Create trigger triggerName2 on types after insert as if(exists(Select * from inserted where len(name)<7)) Begin raiserror('You can write error message here',7,2) Rollback transaction End |
Example 5: The trigger prevents to delete more than one records on borrows table at one time.
Solution
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Create trigger borrowsDelete on borrows for delete as Begin Declare @sayi int = (Select count(*) from deleted) if(@sayi>1) Begin raiserror('You can delete only one record',7,2) Rollback transaction End End |
Example 6: Trigger that prevents updating the student names.
Solution 1
|
1 2 3 4 5 6 7 8 9 |
Create trigger triggerName3 on students for update as if(exists(Select * from inserted,deleted where inserted.studentId = deleted.studentId and inserted.name != deleted.name)) Begin raiserror('You can not update student names',7,2) Rollback transaction End |
Solution 2
|
1 2 3 4 5 6 7 8 |
Create trigger triggerName4 on students for update as if(update(name)) Begin raiserror('You can not update student names',7,2) Rollback transaction End |
Example 7: The trigger, adds points to students that up to the points of the book.
Solution
|
1 2 3 4 5 6 7 8 9 |
Create trigger triggerName5 on borrows after insert as Begin Declare @stdID int = (Select studentId from inserted) Declare @point int = (Select point from books where bookId = (Select bookId from inserted)) Update students set point += @point where studentId = @stdID End |
Example 8: The trigger prevents to delete the student. It updates the student’s points to zero.
Solution
|
1 2 3 4 5 6 7 |
Create trigger triggerName6 on students instead of delete as Begin Update students set point = 0 where studentId in ( Select studentId from deleted) End |
Example 9: The trigger prevents to take book(type drama) of students which class level nine
Solution
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Create trigger triggerName7 on borrows after insert as Begin if(exists(Select * from inserted where studentId in (Select studentId from students where class like '%9%') and bookId in (Select books.bookId from books,types where books.typeId = types.typeId and types.name = 'Dram'))) Begin raiserror('You can write error message here',7,2) Rollback transaction End End |
Example 10: The trigger lets:The length of student names must be between 5 and 10 characters.
Solution
|
1 2 3 4 5 6 7 8 9 10 |
Create trigger triggerName8 on students after insert as Begin if(not exists(Select * from inserted where len(name) between 3 and 8)) begin raiserror('You can write error message here',7,2) Rollback transaction End End |
Example 11: The trigger prevents to lend books that has not been brought yet.
Solution
|
1 2 3 4 5 6 7 8 9 10 11 |
Create trigger triggerName9 on borrows after insert as begin if(exists(Select * from borrows where bookId in(Select bookId from inserted) and broughtDate is null and borrowId<>@@IDENTITY)) Begin raiserror('You can write error message here',7,2) Rollback transaction End End |
Example 12: When a student add to student table,add a borrow record with randomly selected book.
Solution 1
|
1 2 3 4 5 6 7 8 9 10 11 |
Create trigger triggerName10 on students after insert as Begin Declare @bookId int Declare @studentId int Select @studentId = studentId from inserted Select @bookId = bookId from books order by newid() Insert into borrows(studentId,bookId,takenDate) values(@studentId,@bookId,getdate()) End |
Solution 2
|
1 2 3 4 5 6 7 8 9 |
Create trigger triggerName15 on students after insert as Begin Declare @bookId int =(Select top 1 bookId from books order by newid()) Insert into borrows(studentId,bookId,takenDate) Select studentId,@bookId,GETDATE() from inserted End |
Example 13: The student’s birthdate who will be added to the students table must be bigger than 2003.
Solution
|
1 2 3 4 5 6 7 8 9 10 |
Create trigger triggerName11 on students after insert as begin if(exists(Select * from inserted where year(birthdate)<2003)) begin raiserror('You can write error message here',7,2) Rollback transaction End End |
Example 14:A student can get only one book in the same day.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Create trigger triggerName12 on borrows after insert as Begin if(exists(Select * from borrows,inserted where datediff(dd,borrows.takendate,inserted.takenDate)=0 and borrows.studentId = inserted.studentId and borrows.borrowId<@@IDENTITY)) Begin raiserror('You have already taken book today',3,5) Rollback transaction End End |
Example 15:Increase the point of each borrowed book by 1 point
Solution
|
1 2 3 4 5 6 7 |
Create trigger triggerName on borrows after insert as Begin Update books set point += 1 where bookId in (select bookId from inserted) End |
