DROP TRIGGER carrier_routes
GO
CREATE TRIGGER carrier_routes
on Route
FOR INSERT, UPDATE
AS
        DECLARE @CarrierCount int

	IF @@rowcount > 0 and UPDATE(CarrierID)
		BEGIN
                        SET @CarrierCount = SELECT count(Route.CarrierID) 
                                            FROM Route, Inserted 
                                            WHERE Route.CarrierID = Inserted.CarrierID
			IF @CarrierCount > 3
				BEGIN
					ROLLBACK TRANSACTION
					RAISERROR	('A carrier cannot have more than 3 routes', 16, 1)
				END


		END
	ELSE
				BEGIN
					PRINT 'Carrier has a new route'
				END