Restriction on number of issued books from the same ID. (SQL)

Question

I have created two tables. First table has information of students such as ‘ID, Name and address. The second table has information of books has book_id and book title. The primary key of first table is ID and it is the foreign key in the second table with reference to the first table. I want to set restriction so that when a student issues more than 3 book, the database should not accept it. Until now I have created this trigger:

Create Trigger trig On tblbooks
After Insert
As
If (Select Count(ID) from tblbooks Where ID = 1) > 3
RollBack Transaction

But the problem with this trigger is that I have to specify the ID of the student which I cannot do for every student that issues a book. I want some help to create a trigger that works for all the students without specifying there ID.

Answer 1

Try

Create OR ALTER Trigger trig On tblbooks  
After Insert  
As  
  
If exists(Select 1 from (select count(b.ID) as cnt FROM tblbooks b  
inner join inserted i on b.book_id = i.book_id group by b.ID having count(b.Id) > 3) x)  
begin  
  
       RollBack Transaction  
        raiserror('Students cannot borrow more than 3 books!', 16, 1)  
end  

Answer 2

In a trigger you have access to the virtual tables inserted and deleted. The inserted table holds the row inserted in an INSERT statement, and for an UPDATE statement, inserted holds the rows as how they look after the UPDATE statement. Analogously, the deleted table holds the row deleted by a DELETE statement, and the beforeimage of the rows updated by an UPDATE statement.

I leave it at that, and leave as an exercise to you how to write the trigger, now that you know the tools to use.