IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIGGER_CustomerHotelNumber]'))
DROP TRIGGER [dbo].[TRIGGER_CustomerHotelNumber]
GO
CREATE TRIGGER dbo.TRIGGER_CustomerHotelNumber ON dbo.Customer AFTER INSERT, UPDATE AS
/*
  Created by Kent Johansen november 2016.
  Prevent illegal records in Customer table
*/
BEGIN
  DECLARE @HotelNumber INT
  DECLARE @CustomerTypeNumber INT

  DECLARE db_cur_inserted CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
    SELECT HotelNumber, CustomerTypeNumber
    FROM INSERTED
  OPEN db_cur_inserted

  FETCH NEXT FROM db_cur_inserted INTO @HotelNumber, @CustomerTypeNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @CustomerTypeNumber <> 7 AND ISNULL(@HotelNumber, -1) >= 0
    BEGIN
      RAISERROR ('HotelNumber may not be 0', 16, 1)
      ROLLBACK TRANSACTION
    END

    FETCH NEXT FROM db_cur_inserted INTO @HotelNumber, @CustomerTypeNumber
  END

  CLOSE db_cur_inserted
  DEALLOCATE db_cur_inserted
END