CREATING THE PHYSICAL MODEL WITH SQL SERVER - A Developer's Guide to Data Modeling for SQL Server - page 218

FOR INSERT, UPDATE
AS
DECLARE @college_count int
SELECT @college_count = COUNT(tbl_college.id)
FROM tbl_college
JOIN tbl_faculty
ON tbl_college.dean_id = tbl_faculty.id
WHERE tbl_faculty.id = (SELECT dean_id FROM INSERTED)
IF @college_count > 1
BEGIN
RAISERROR('This faculty member is dean of another
college',11,1)
ROLLBACK
END
As you can see, this code evaluates the number of colleges the faculty
member is dean over, and if that number is more than 1, the transaction is
canceled.
This trigger evaluates the number of colleges after the actual insert has
occurred on tbl_college, so there could be a lot of performance overhead
if this were a larger table. To save on this overhead, we could have used an
INSTEAD OF trigger to accomplish the same thing. The difference is that
we would be able to evaluate the change before the insert occurs, and that
would prevent the unnecessary insert if the transaction is rolled back. You
can have only one INSTEAD OF trigger in a table for each action, so this
may or may not be an option for you.
You see how easy it is to add code using triggers to help implement ad-
vanced business rules. Now for a word of caution. Triggers can be great
tools, but overuse can cause poor database performance. Although you can
implement advanced cardinality using tools inside SQL Server, you need
to consider your options carefully. Will the data be unusable if these rules
are not followed? The answer is, probably not. Unlike missing credit card
data, having too many deans is an easy thing to fix. If you have a real need
to conform to some set of cardinality rules, then by all means go ahead and
do it, but be careful. In a perfect world, a well-constructed business rules
layer in your application will always outperform database triggers. If your
application has that functionality, take advantage of it.