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

GO


CREATE TRIGGER [CatchBlank1StDayPriceReservations] ON [dbo].[RESERVATIONS] 
/*
  Author: Brian Andersen
  Date: 2008.02.27
  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.

*/

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 @Price Money
  DECLARE @TotalPrice Money
  DECLARE @Text VarChar(200)

  SELECT @ReferenceNumber = ReferenceNumber, @GuestNumber = GuestNumber, @Price = Price, @TotalPrice = TotalPrice FROM INSERTED

  IF (IsNull(@TotalPrice, 0) > 0) AND (IsNull(@Price, 0) = 0) 
  BEGIN
    SET @Text = 
      'Silently: 1StDay price is BLANK on Reservations: ' +
      'ReferenceNumber = ' + Convert(VarChar(20), @ReferenceNumber) + 
      ', GuestNumber: ' + Convert(VarChar(20), @GuestNumber) +
      ', 1StDay Price: ' + Convert(VarChar(20), IsNull(@Price, -1)) +
      ', TotalPrice: ' + Convert(VarChar(20), @TotalPrice)

    RAISERROR(@Text, 15, 1)
  END
END