IF EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'dbo.ConstraintAddressTypeEquals2RA') AND OBJECTPROPERTY(id, N'IsTrigger') = 1) 
  DROP TRIGGER dbo.ConstraintAddressTypeEquals2RA 
GO
CREATE TRIGGER [dbo].[ConstraintAddressTypeEquals2RA] 
   ON  [dbo].[ReservationAddressXTab] 
   FOR INSERT, UPDATE
AS 
BEGIN
  SET NOCOUNT ON;
  DECLARE 
    @ReferenceNumber int,
	@GuestNumber int,
    @AddressNumber int,
	@AddressType int,
	@AddressTypeX int,
	@Msg VARCHAR(200)
  SET @Msg = NULL
  SELECT @AddressNumber = ADDRESSNUMBER, @AddressTypeX = AddressTypeNumber, @ReferenceNumber= ReferenceNumber, @GuestNumber = GuestNumber FROM INSERTED
  SET @AddressNumber = ISNULL(@AddressNumber, 0)
  SET @AddressTypeX = ISNULL(@AddressTypeX, 0)
  IF @AddressNumber > 0
  BEGIN
    SELECT @AddressType = AddressType FROM ReservationAddress WITH (NOLOCK) 
    WHERE AddressNumber = @AddressNumber

    IF (SELECT COUNT(*) FROM ReservationAddressXtab WITH (NOLOCK)
	  WHERE AddressNumber = @AddressNumber and ReferenceNumber <> @ReferenceNumber) > 0
	BEGIN
	--DO NOT ALLOW DUPLICATES ON Different Referances
  	  DELETE ReservationAddressXTab  
	  WHERE AddressNumber = @AddressNumber AND ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
      SET @Msg = 'Violation of Address not shareable on differnet reservations (Reference numbers). Cannot insert/update duplicate key. The duplicate key value is ReferenceNumber;AddressNumber='
	  + CONVERT(VARCHAR, @ReferenceNumber)+';' + CONVERT(VARCHAR, @AddressNumber)
	END
	ELSE IF (@AddressType) <> @AddressTypeX 
	BEGIN
	  --PATCH 4 DUPLICATES AND WRONG TYPE
      UPDATE ReservationAddressXTab SET AddressTypeNumber = @AddressType 
	  WHERE AddressNumber = @AddressNumber AND ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
      SET @Msg = 'RA.AddressType <> RAX.AddressTypeNumber ('+CONVERT(VARCHAR, @AddressType)+'<>'+CONVERT(VARCHAR, @AddressTypeX)+
	    '): XTab.AddressType patched cause mismatch error on ReferenceNumber;GuestNumber;AddressNumber='+ CONVERT(VARCHAR, @ReferenceNumber)+';'+ CONVERT(VARCHAR, @GuestNumber)+';'+ CONVERT(VARCHAR, @AddressNumber)
	END
  END
  IF ISNULL(@Msg, '') <> ''
    RAISERROR (@Msg, 15, 1);   
END
