In this post We will write some trigger examples on library database. There will be about 30 examples in this article. I hope you will like this examples. This examples will help you to learn how to write triggers.
Example 1: Trigger prevents the entry of data to student table
|
1 2 3 4 5 6 7 |
Create Trigger Tr_NotAllowedAddingStudents on Students Instead of Insert As Begin raiserror('Adding students canceled',2,3); End |
——-Method 2
|
1 2 3 4 5 6 7 8 |
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 10A
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Create Trigger Tr_NotAllowedAddingStudentsto10AClass on Students After Insert As Begin If (exists(select * from inserted where class ='10A')) 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
|
1 2 3 4 5 6 7 8 |
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 six characters
|
1 2 3 4 5 6 7 |
Create trigger triggerName2 on types after insert as if(exists(Select * from inserted where len(name)<6)) 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
|
1 2 3 4 5 6 7 8 9 10 |
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
|
1 2 3 4 5 6 7 |
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 |
——–Method 2
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 7 |
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
|
1 2 3 4 5 |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 2 3 4 5 6 7 8 9 |
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 |
———-Method 2
|
1 2 3 4 5 6 7 |
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
|
1 2 3 4 5 6 7 8 |
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 |
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
|
1 2 3 4 5 |
Create trigger triggerName on borrows after insert as Begin Update books set point += 1 where bookId in (select bookId from inserted) End |
