-- 1.	An Insert/Update trigger on the Customer table to ensure that the RouteID is valid. 
-- Should an invalid RouteID be entered, raise an error and rollback the transaction. 
-- Disable the constraint(s) required to test this trigger. Show all the code required to do this.

-- Question #1
DROP TRIGGER Valid_Route_ID
GO
CREATE TRIGGER Valid_Route_ID
ON Customer
FOR INSERT, UPDATE AS
	IF @@RowCount > 0 and UPDATE(RouteID)
		BEGIN
			IF NOT Exists(	SELECT RouteID
					FROM   Route
					WHERE  RouteID = Inserted.RouteID
				     )
				BEGIN
					ROLLBACK TRANSACTION
					RAISERROR('Route ID is not valid', 16, 1)
				END
			-- ENDIF
		END	
RETURN	
GO