IF EXISTS (SELECT * FROM dbo.SysObjects WHERE id = object_id(N'dbo.TRIGGER_ReservationsLastUpdDateStamp') AND OBJECTPROPERTY(id, N'IsTrigger') = 1) DROP TRIGGER dbo.TRIGGER_ReservationsLastUpdDateStamp 
GO
CREATE TRIGGER [dbo].[TRIGGER_ReservationsLastUpdDateStamp] ON [dbo].[RESERVATIONS] AFTER INSERT,UPDATE AS
BEGIN
  DECLARE @ReferenceNumber INT
  DECLARE @GuestNumber INT
  DECLARE @StatusOld TINYINT
  DECLARE @StatusNew TINYINT

  DECLARE @SystemDateTime DATETIME
  EXEC SystemDateTime '', @SystemDateTime OUTPUT

  DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT i.ReferenceNumber, i.GuestNumber, i.Status, d.Status
    FROM inserted i
    LEFT OUTER JOIN Deleted d ON i.ReferenceNumber = d.ReferenceNumber
      AND i.GuestNumber = d.GuestNumber
  OPEN db_cur

  FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestNumber, @StatusNew, @StatusOld

  WHILE @@FETCH_STATUS = 0
  BEGIN
    UPDATE ReservationsExt SET LastUpdDateStamp = GETDATE() 
    WHERE ReservationsExt.FK_ReferenceNumber = @ReferenceNumber
    AND ReservationsExt.FK_GuestNumber = @GuestNumber

    IF @@ROWCOUNT = 0
      INSERT INTO ReservationsExt (FK_ReferenceNumber, FK_GuestNumber, LastUpdDateStamp) VALUES (@ReferenceNumber, @GuestNumber, GETDATE())

    IF @StatusNew IN (0,2) AND ISNULL(@StatusOld, 99) NOT IN (0,2)
      UPDATE ReservationsExt SET ConfirmDateStamp = @SystemDateTime
      WHERE ReservationsExt.FK_ReferenceNumber = @ReferenceNumber
      AND ReservationsExt.FK_GuestNumber = @GuestNumber

    FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestNumber, @StatusNew, @StatusOld
  END
  CLOSE db_cur
  DEALLOCATE db_cur
END
