IF EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'dbo.ReservationAddressAfterUpdate') AND OBJECTPROPERTY(id, N'IsTrigger') = 1) DROP TRIGGER dbo.ReservationAddressAfterUpdate 
GO
CREATE TRIGGER ReservationAddressAfterUpdate ON ReservationAddress AFTER INSERT, UPDATE AS 
SET NOCOUNT ON

DECLARE @EventsNightAuditAlwaysSetPassed BIT

SELECT @EventsNightAuditAlwaysSetPassed = CONVERT(BIT, KeyValue) FROM HotelSetup(NOLOCK) WHERE Searchkey = 'EventsNightAuditAlwaysSetPassed'
SET @EventsNightAuditAlwaysSetPassed = ISNULL(@EventsNightAuditAlwaysSetPassed,0)

IF @EventsNightAuditAlwaysSetPassed = 1
BEGIN
  DECLARE @CustomerNumber INT
  DECLARE @AddressNumber INT
  DECLARE @ReferenceNumber INT
  DECLARE @GuestNumber INT
  DECLARE @ReservationConfLineNumber INT
  DECLARE @ProductNumber INT
  DECLARE @IsAttendeeReservation BIT
  DECLARE @InternalCourseProduction BIT
  DECLARE @RefCount INT

  DECLARE tmpCursor CURSOR FOR
    SELECT AddressNumber, LINK FROM inserted
  OPEN tmpCursor
  FETCH NEXT FROM tmpCursor INTO @AddressNumber, @CustomerNumber
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SELECT @ReferenceNumber = ReferenceNumber, @GuestNumber = GuestNumber FROM RESERVATIONADDRESSXTAB WITH (NOLOCK) WHERE ADDRESSNUMBER = @AddressNumber AND AddressTypeNumber = 1

    SELECT @RefCount = COUNT(*) FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumberSub = @ReferenceNumber
    IF @RefCount = 1 /* If it is an Attendee reservation we should continue */
    BEGIN 
      SELECT @ReservationConfLineNumber = ReservationConfLineNumber FROM ReservationMeetingXTab WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
      SELECT @ProductNumber = ProductNumber FROM RESERVATIONCONFERENCELINE WITH (NOLOCK) WHERE ReservationConfLineNumber = @ReservationConfLineNumber

  	  IF (ISNULL(@ProductNumber,0) > 0) AND (ISNULL(@CustomerNumber, 0) > 0)
	  BEGIN
        UPDATE CustomerProductPassed
	    SET IsProductPassed = 1,
  	      IsProductPassedDate = GETDATE()
	    WHERE FK_CustomerNumber = @CustomerNumber
	      AND FK_ProductNumber = @ProductNumber

        IF @@ROWCOUNT = 0 /* Nothing was updated, so we need to insert */
          INSERT INTO CustomerProductPassed (FK_CustomerNumber, FK_ProductNumber, IsProductPassed, IsProductPassedDate) VALUES (@CustomerNumber, @ProductNumber, 1, GETDATE())
	  END
	END

    UPDATE ReservationMeetingXTab
    SET IsProductPassed = 1 
    WHERE ReferenceNumber = @ReferenceNumber 
      AND GuestNumber = @GuestNumber
      AND ReservationConfLineNumber = @ReservationConfLineNumber

    FETCH NEXT FROM tmpCursor INTO @AddressNumber, @CustomerNumber
  END
  CLOSE tmpCursor
  DEALLOCATE tmpCursor
END
