IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIGGER_CleanStatusUpdate]'))
DROP TRIGGER [dbo].[TRIGGER_CleanStatusUpdate]
GO
CREATE TRIGGER dbo.TRIGGER_CleanStatusUpdate ON dbo.Rooms AFTER INSERT, UPDATE AS
/*
  20210805 - KC: only insert record into RoomCleaningStatus if record not exists	

  Created by Kent Johansen, October 2019
  Update RoomCleaningDate when clean status is changed

*/
BEGIN
  SET NOCOUNT ON

  IF UPDATE (CleanStatus)
  BEGIN
    DECLARE @RoomNumber INT
    DECLARE @CleanStatus INT

    DECLARE db_cur_inserted CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
      SELECT RoomNumber, CleanStatus
	    FROM INSERTED
    OPEN db_cur_inserted

    FETCH NEXT FROM db_cur_inserted INTO @RoomNumber, @CleanStatus

    WHILE @@FETCH_STATUS = 0
    BEGIN
	  IF NOT EXISTS(SELECT * FROM dbo.RoomCleaningDate WHERE RoomNumber = @RoomNumber AND CleaningDate = GETDATE() AND CleanStatus = @CleanStatus)
		INSERT INTO dbo.RoomCleaningDate (RoomNumber, CleaningDate, CleanStatus) VALUES (@RoomNumber, GETDATE(), @CleanStatus)
      FETCH NEXT FROM db_cur_inserted INTO @RoomNumber, @CleanStatus
    END

    CLOSE db_cur_inserted
    DEALLOCATE db_cur_inserted
  END
END