IF EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'dbo.CreateBonuscardRelation') AND OBJECTPROPERTY(id, N'IsTrigger') = 1) DROP TRIGGER dbo.CreateBonuscardRelation 
GO
CREATE TRIGGER dbo.CreateBonuscardRelation 
   ON  dbo.Folios 
   AFTER INSERT
AS 
BEGIN
  SET NOCOUNT ON;
  declare 
    @HotelCustomerNumber int,
    @AddressNumber int,
    @ReferenceNumber int,
    @GuestNumber int,
    @FolioNumber int
  select @HotelCustomerNumber = HotelCustomerNumber, @AddressNumber = ISNULL(ReservationAddressNumber, 0), @ReferenceNumber = ISNULL(ReferenceNumber, 0),
    @GuestNumber = ISNULL(GuestNumber, 0), @FolioNumber = FolioNumber
  from INSERTED

  if (select dbo.GetHotelSetupKeyValue('BonusSystemEnabled', @HotelCustomerNumber)) = 1 --bonuscard system enabled
    and (select dbo.GetHotelSetupKeyValue('UseBonusCard', @HotelCustomerNumber)) = 1    --bonuscard system 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 
	  @AddressType int,
  	  @CustomerNumber int,
	  @PK_Card int
    select TOP 1 @AddressType = isnull(AddressType, 0), @CustomerNumber = isnull(Link, 0)  from ReservationAddress (NOLOCK) where AddressNumber = @AddressNumber
    --on guest address and customer related only
    if @AddressType  = 1 and @CustomerNumber > 0
    begin
	  select TOP 1 @PK_Card = ISNULL(FK_Card, 0) from CustomerCardXTab (NOLOCK) where CustomerNumber = @CustomerNumber and LogNumberDelete IS NULL
	  --find attached card on the customer and create the relation to the folio
	  if @PK_Card > 0
	  begin
	    declare
		  @UserIdNumber int,
		  @LogNumber int
	    select top 1 @UserIdNumber = UserIdNumber from Personel (nolock) order by UserIdNumber
	    insert into LogPicasso (DateStamp, UserIdNumber, Text) 
		  values (GetDate(), @UserIdNumber, 'Assigned card '+ convert(varchar, @PK_Card) + ' to Folio ' + convert(varchar, @FolioNumber))
	    select @LogNumber = @@identity
	    INSERT INTO FolioCardXTab (FolioNumber, FK_Card, LogNumberCreate) VALUES (@FolioNumber, @PK_Card, @LogNumber)
	  end
    end
  end
END
