SQL Server 2012 Trigger

Question

I have a small little thing with SQL that's been bothering me now for a while, let's say I have two tables (Customer and Loan). However, I want a trigger that's checking based on the Borrowertype attribute. I suppose with the second query after AND I need something to check whether the userID in Loans are the same as the one in Customer, but must be messing it up or I'm completely thinking this the wrong way.

CREATE TABLE Customer 
(
     userID int identity primary key, 
     Name varchar(20),
     Borrowertype varchar(20)
);

CREATE TABLE Loan 
(
     Id int identity primary key, 
     userID int,
     FOREIGN KEY (userID) REFERENCES Customer(userID)
);

IF OBJECT_ID ('Customer.maximum_books_per_user','TR') IS NOT NULL
  DROP TRIGGER Customer.maximum_books_per_user;
GO

CREATE TRIGGER maximum_books_per_user ON Customer
AFTER INSERT
AS
IF (SELECT Borrowertype FROM Customer) = 'diffborrowertypehere' 
    AND (SELECT COUNT(*) FROM inserted AS i JOIN Customer AS c 
        ON ??? WHERE ???
        ) > 5
BEGIN
   ROLLBACK TRANSACTION
   RAISERROR('You have reached maximum allowed loans.', 16, 1)
END
GO

Answer 1

Your trigger needs to be on the Loan table, as that's where a row would be being inserted that could be rejected. Something like this:

EDIT: rewritten to handle inserts for multiple Customers at once

CREATE TRIGGER maximum_books_per_user ON Loan
FOR INSERT
AS
-- Fail if there are any customers that will have more than the maximum number of loans
IF EXISTS (
    SELECT i.userID, COUNT(*)
    FROM inserted i
    JOIN Loan l
        ON i.userID = l.userID
    GROUP BY i.userID
    HAVING COUNT(*) >= 5
)
BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('You have reached maximum allowed loans.', 16, 1)
END