IF EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'dbo.ConstraintFolioAddressRelForInsertUpdate') AND OBJECTPROPERTY(id, N'IsTrigger') = 1) DROP TRIGGER dbo.ConstraintFolioAddressRelForInsertUpdate 
GO
CREATE TRIGGER [dbo].[ConstraintFolioAddressRelForInsertUpdate] 
   ON  [dbo].[Folios] 
   FOR INSERT, UPDATE
AS 
BEGIN
  SET NOCOUNT ON;
  DECLARE 
    @FolioNumberOld int,
    @AddressNumber int,
	@AddressType int,
    @ReferenceNumber int,
    @GuestNumber int,
    @FolioNumber int,
	@RecCount int,
	@Msg VARCHAR(100)
  SET @Msg = NULL
  SELECT @AddressNumber = ISNULL(ReservationAddressNumber, 0), @ReferenceNumber = ISNULL(ReferenceNumber, 0),
    @GuestNumber = ISNULL(GuestNumber, 0), @FolioNumber = FolioNumber
  FROM INSERTED
  SET @FolioNumber = ISNULL(@FolioNumber, 0)
  SET @AddressNumber = ISNULL(@AddressNumber, 0)
  SET @ReferenceNumber = ISNULL(@ReferenceNumber, 0)
  IF @FolioNumber > 0
  BEGIN
    SELECT @RecCount = COUNT(*) FROM Folios WITH (NOLOCK) 
    WHERE FolioNumber <> @FolioNumber AND ReservationAddressNumber = @AddressNumber AND ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
    IF (@RecCount) > 0 AND (@AddressNumber > 0 OR @ReferenceNumber > 0)
	BEGIN
	  IF @RecCount = 1
	  BEGIN
        SELECT @FolioNumberOld = FolioNumber
        FROM Folios WITH (NOLOCK)
        WHERE ReservationAddressNumber = @AddressNumber AND ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
		IF @ReferenceNumber > 0
		  SET @Msg = 'DuplRef: Folio Rec Rel to this Address ('+ 
		    CONVERT(VARCHAR, @AddressNumber)+') is not allowed. XInfo Ref ('+
		    CONVERT(VARCHAR, @ReferenceNumber)+'/'+CONVERT(VARCHAR, @GuestNumber)+'). Folio ('+
		    CONVERT(VARCHAR, @FolioNumberOld)+') exists!'
		ELSE IF @AddressNumber > 0
		  SET @Msg = 'DuplInv: Folio Rec Rel to this Address ('+ 
		    CONVERT(VARCHAR, @AddressNumber)+') is not allowed. Folio ('+
		    CONVERT(VARCHAR, @FolioNumberOld)+') exists!'
      END
	  ELSE
		SET @Msg = 'DuplMulti: Multiple Rec Rel to this Address ('+ 
		  CONVERT(VARCHAR, @AddressNumber)+'). XInfo Ref ('+
		  CONVERT(VARCHAR, @ReferenceNumber)+'/'+CONVERT(VARCHAR, @GuestNumber)+')!'
    END

	IF @ReferenceNumber > 0
	BEGIN
	  SELECT @RecCount = COUNT(*) FROM Folios WITH (NOLOCK) WHERE ReservationAddressNumber = @AddressNumber AND ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
      IF @RecCount = 1
	  BEGIN
		SELECT @AddressType = AddressType FROM dbo.ReservationAddress WITH (NOLOCK) WHERE AddressNumber = @AddressNumber
		SELECT @RecCount = COUNT(*) FROM dbo.Folios F WITH (NOLOCK)
		  INNER JOIN dbo.ReservationAddress A WITH (NOLOCK) ON A.AddressNumber = F.ReservationAddressNumber
		WHERE F.ReferenceNumber = @ReferenceNumber AND F.GuestNumber = @GuestNumber AND A.ADDRESSTYPE = @AddressType AND F.FolioNumber <> @FolioNumber
  	    IF @RecCount = 1
	  	BEGIN
		  SELECT Top 1 @AddressNumber = F.ReservationAddressNumber, @FolioNumberOld = F.FolioNumber FROM dbo.Folios F WITH (NOLOCK)
		    INNER JOIN dbo.ReservationAddress A WITH (NOLOCK) ON A.AddressNumber = F.ReservationAddressNumber
		  WHERE F.ReferenceNumber = @ReferenceNumber AND F.GuestNumber = @GuestNumber AND A.ADDRESSTYPE = @AddressType AND F.FolioNumber <> @FolioNumber
		  ORDER BY F.FolioNumber DESC
  		  SET @Msg = 'Duplicate New Folio ('+
			CONVERT(VARCHAR, @FolioNumber)+') AddressType already Exist ('+ 
		    CONVERT(VARCHAR, @AddressType)+', '+CONVERT(VARCHAR, @AddressNumber)+') on Folio('+
			CONVERT(VARCHAR, @FolioNumberOld)+')/Ref ('+
		    CONVERT(VARCHAR, @ReferenceNumber)+'/'+CONVERT(VARCHAR, @GuestNumber)+')!'
		END
		ELSE IF @RecCount > 1
  		  SET @Msg = 'Multiple Duplicate AddressType ('+ 
		    CONVERT(VARCHAR, @AddressType)+', '+CONVERT(VARCHAR, @AddressNumber)+') on Ref ('+
		    CONVERT(VARCHAR, @ReferenceNumber)+'/'+CONVERT(VARCHAR, @GuestNumber)+')!'
	  END
 	  ELSE IF ISNULL(@RecCount, 0) > 0
		SET @Msg = 'Duplicate Folio not allowed Address ('+ 
		  CONVERT(VARCHAR, @AddressNumber)+'). Folio already exist on Ref ('+
		  CONVERT(VARCHAR, @ReferenceNumber)+'/'+CONVERT(VARCHAR, @GuestNumber)+')!'
	END  
  END
  IF ISNULL(@Msg, '') <> ''
    RAISERROR (@Msg, 15, 1);   

END

GO


