
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[ReservationMainForInsertUpdate]'))
DROP TRIGGER [dbo].[ReservationMainForInsertUpdate]
GO
CREATE TRIGGER [dbo].[ReservationMainForInsertUpdate] 
   ON  [dbo].[ReservationMain] 
   FOR INSERT, UPDATE NOT FOR REPLICATION
AS 
BEGIN
  SET NOCOUNT ON
  IF UPDATE(HotelNumber)
  BEGIN
    DECLARE @HCN INT, @HCN_Old INT, @ReferenceNumber INT
	SELECT @HCN = C.CustomerNumber FROM inserted NEW, CUSTOMER C
	WHERE NEW.HotelNumber = C.HotelNumber AND C.CustomerTypeNumber = 7 AND ISNULL(C.MarkAsDeleted, 0) = 0  
	SELECT @ReferenceNumber = NEW.ReferenceNumber FROM inserted NEW
	IF ISNULL(@HCN, 0) > 0
	BEGIN
      SELECT @HCN_Old = RM.FkCustomerHotel 
      FROM ReservationMain RM
	  WHERE RM.ReferenceNumber = @ReferenceNumber

      UPDATE ReservationMain SET FkCustomerHotel = @HCN
      FROM ReservationMain RM
	  WHERE RM.ReferenceNumber = @ReferenceNumber
	  IF (SELECT Count(*) FROM Folios F (NOLOCK) 
	    WHERE ReferenceNumber = @ReferenceNumber AND HotelCustomerNumber = @HCN_Old) > 0 --necessary to avoid locking issue
	    UPDATE Folios SET HotelCustomerNumber = @HCN
	      WHERE ReferenceNumber = @ReferenceNumber AND HotelCustomerNumber = @HCN_Old
	END

  END
END
