IF EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'dbo.TRIGGERInvalidRoomLineForOOO') AND OBJECTPROPERTY(id, N'IsTrigger') = 1) DROP TRIGGER dbo.TRIGGERInvalidRoomLineForOOO 
GO
CREATE TRIGGER [dbo].[TRIGGERInvalidRoomLineForOOO] ON [dbo].[RESERVATIONROOMLINEXTab] AFTER INSERT, UPDATE AS 

DECLARE @Ref INT
DECLARE @ErrMsg NVARCHAR(50)
DECLARE @RoomNumber INT

IF UPDATE(LineNumber)
BEGIN
  SELECT @Ref = ReferenceNumber FROM RESERVATIONROOMLINEXTab WITH (NOLOCK) WHERE LineNumber = (SELECT LineNumber FROM Inserted)
  SELECT @RoomNumber = RoomNumber FROM RESERVATIONROOMLINE WITH (NOLOCK) WHERE LineNumber = (SELECT LineNumber FROM Inserted)

  IF @Ref = 0 AND ISNULL(@RoomNumber, 0) = 0
  BEGIN
    SET @ErrMsg = N'Invalid OOO RoomLine record. Line Number: ' + CAST((SELECT LineNumber FROM Inserted) AS VARCHAR)
    ROLLBACK TRANSACTION
    RAISERROR(@ErrMsg, 15, 1)
    RETURN
  END
END 
