if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CatchBlank1StDayPriceReservationRoomLine]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[CatchBlank1StDayPriceReservationRoomLine]

GO


CREATE TRIGGER [CatchBlank1StDayPriceReservationRoomLine] ON [dbo].[RESERVATIONROOMLINE] 
/*
  Author: Brian Andersen
  Date: 2008.02.22
  Purpose: Raise a silent error when ever 1StDay price is blank or Zero and TotalPrice is <> 0. 
  Caution: The Trigger works with all Picasso editions compiled after 20080227.
           The Trigger is by default disabled. See further down.

  Test code to be used in Delphi on the ReservationFormUnit. Place one TButton and on TEdit component on the form.

  procedure TReservationBox.Button1Click(Sender: TObject);
  const
    SQL =
      'UPDATE RESERVATIONROOMLINE ' +
      'SET    Price = :Price ' +
      'FROM   RESERVATIONROOMLINE WITH (nolock) ' +
      '  INNER JOIN ' +
      '    RESERVATIONROOMLINEXTAB WITH (nolock) ON ' +
      '    RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER ' +
      'WHERE (RESERVATIONROOMLINEXTAB.ReferenceNumber = :ReferenceNumber) AND ' +
      '      (RESERVATIONROOMLINEXTAB.GuestNumber = :GuestNumber)';
  var
    Q: TExtQuery;
    S: TXState;
  begin
    S := TBaseX.StateBegin('TReservationBox.Button1Click');
    Q := TExtQuery.Create(nil, S);
    try
      Q.SQL.Text := SQL;
      Q.ParamByName('Price').AsInteger := StrToInt(Edit1.Text);
      Q.ParamByName('ReferenceNumber').AsInteger := FReferenceNumber;
      Q.ParamByName('GuestNumber').AsInteger := FGuestNumber;
      Q.ExecSQL;
      S.Commit;
    finally
      TBaseX.StateEnd(S, False);
      Q.Free;
    end;
  end;
*/

FOR INSERT, UPDATE, DELETE 
AS
IF UPDATE(Price)
BEGIN
  RETURN -- This trigger is disabled as default. You must manually activate it by removing this line!

  DECLARE @ReferenceNumber INT
  DECLARE @GuestNumber INT
  DECLARE @LineNumber INT
  DECLARE @Price Money
  DECLARE @TotalPrice Money
  DECLARE @Text VarChar(200)

  SELECT @LineNumber = LineNumber, @Price = Price, @TotalPrice = TotalPrice FROM INSERTED

  IF (IsNull(@TotalPrice, 0) > 0) AND (IsNull(@Price, 0) = 0) 
  BEGIN
    SELECT @ReferenceNumber = ReferenceNumber,  @GuestNumber = GuestNumber 
    FROM ReservationRoomlineXTab(nolock) INNER JOIN ReservationRoomline(nolock) ON ReservationRoomlineXTab.LineNumber = ReservationRoomline.LineNumber 
    WHERE ReservationRoomLineXTAB.LineNumber = @LineNumber

    SET @Text = 
      'Silently: 1StDay price is BLANK on RESERVATIONROOMLINE: ' +
      'ReferenceNumber = ' + Convert(VarChar(20), @ReferenceNumber) + 
      ', GuestNumber: ' + Convert(VarChar(20), @GuestNumber) +
      ', LineNumber: ' + Convert(VarChar(20), @LineNumber) +
      ', 1StDay Price: ' + Convert(VarChar(20), IsNull(@Price, -1)) +
      ', TotalPrice: ' + Convert(VarChar(20), @TotalPrice)

    RAISERROR(@Text, 15, 1)
  END
END