IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIGGER_AddressAfterInsertUpdateDelete]'))
DROP TRIGGER [dbo].[TRIGGER_AddressAfterInsertUpdateDelete]
GO
CREATE TRIGGER dbo.TRIGGER_AddressAfterInsertUpdateDelete ON dbo.ReservationAddress AFTER INSERT, UPDATE AS
/*
  NB20191011 Created
  FolioBonuscardUpdate from Reservation Guest Address
*/
BEGIN
  SET NOCOUNT ON
  DECLARE @HcnNew INT, @AddressNumberNew INT, @ReferenceNumberNew INT, @GuestNumberNew INT, @CustomerNumberNew INT 
  SELECT @HcnNew = ISNULL(RM.FkCustomerHotel, 0), @AddressNumberNew = ISNULL(F.ReservationAddressNumber, 0)
  , @ReferenceNumberNew = ISNULL(RAX.ReferenceNumber, 0), @GuestNumberNew = ISNULL(RAX.GuestNumber, 0), @CustomerNumberNew = RA.Link
  FROM dbo.ReservationMain RM (NOLOCK) 
    INNER JOIN dbo.ReservationAddressXTab RAX (NOLOCK) ON RM.ReferenceNumber = RAX.ReferenceNumber
    INNER JOIN INSERTED RA ON RAX.AddressNumber = RA.AddressNumber AND RA.AddressType = 1
	LEFT JOIN dbo.Folios F (NOLOCK) ON F.ReservationAddressNumber = RA.AddressNumber
  DECLARE @HcnOld INT, @AddressNumberOld INT, @ReferenceNumberOld INT, @GuestNumberOld INT, @CustomerNumberOld INT
  SELECT @HcnOld = ISNULL(RM.FkCustomerHotel, 0), @AddressNumberOld = ISNULL(F.ReservationAddressNumber, 0)
  , @ReferenceNumberOld = ISNULL(RAX.ReferenceNumber, 0), @GuestNumberOld = ISNULL(RAX.GuestNumber, 0), @CustomerNumberOld = RA.Link
  FROM dbo.ReservationMain RM (NOLOCK) 
    INNER JOIN dbo.ReservationAddressXTab RAX (NOLOCK) ON RM.ReferenceNumber = RAX.ReferenceNumber
    INNER JOIN DELETED RA ON RAX.AddressNumber = RA.AddressNumber AND RA.AddressType = 1
	LEFT JOIN dbo.Folios F (NOLOCK) ON F.ReservationAddressNumber = RA.AddressNumber
  DECLARE @Hcn INT, @ReferenceNumber INT, @GuestNumber INT, @AddressNumber INT 
  IF ISNULL(@HcnNew, 0) > 0 
    SET @Hcn = @HcnNew
  ELSE
    SET @Hcn = @HcnOld
  IF ISNULL(@ReferenceNumberNew, 0) > 0 
    SET @ReferenceNumber = @ReferenceNumberNew
  ELSE
    SET @ReferenceNumber = @ReferenceNumberOld
  IF ISNULL(@GuestNumberNew, 0) > 0 
    SET @GuestNumber = @GuestNumberNew
  ELSE
    SET @GuestNumber = @GuestNumberOld
  IF ISNULL(@AddressNumberNew, 0) > 0 
    SET @AddressNumber = @AddressNumberNew
  ELSE
    SET @AddressNumber = @AddressNumberOld
  IF (SELECT dbo.GetHotelSetupKeyValue('BonusSystemEnabled', @Hcn)) = 1   --bonuscard system enabled and -user enabled
    --on subreservations or single reservations 
    AND (@ReferenceNumber > 0 and (@GuestNumber > 0 or (SELECT COUNT(*) as ResCount FROM Reservations (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND    
      ISNULL(ArrivalDate, '') <> '') = 1) --nb20080924 Exclude invalid records.  
	--or on invoices 
	OR (@ReferenceNumber = 0 and @AddressNumber > 0)) 
  BEGIN
    DECLARE @PkCardNew INT, @PkCardOld INT 
    SELECT TOP 1 @PkCardNew = FK_Card FROM dbo.CustomerCardXTab (NOLOCK) 
    WHERE CustomerNumber = @CustomerNumberNew
    SELECT TOP 1 @PkCardOld = FK_Card FROM dbo.CustomerCardXTab (NOLOCK) 
    WHERE CustomerNumber = @CustomerNumberOld
    EXEC dbo.FolioBonuscardUpdate @ReferenceNumber, @GuestNumber, @PkCardOld, @PkCardNew
  END
END