-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Matthew Dunn>
-- Create date: <Create Date,,>
-- Description:	<Prevents occluding dates to be stored in the 'flat' period table>
-- =============================================
CREATE TRIGGER PreventOccludedFlatSeasons
   ON  ProductSeasonFlat
   FOR  INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Prevent occlusions in the flat table
declare  @StartDate SmallDateTime
declare  @EndDate SmallDateTime

if exists
(
	select ProductSeasonFlatId from inserted where  
	exists	--Any occlusions exist is a failure
		(
			--Any items in the real table which are occluded by anything in inserted.
			Select ProductSeasonFlatId from ProductSeasonFlat
			where
				(
					     (StartDate <= inserted.EndDate)
					 and (inserted.StartDate <= EndDate)
				) 
				and not (
						(StartDate = inserted.EndDate) 
					and 
						(inserted.StartDate = EndDate)
						)
				and (ProductSeasonFlatId <> inserted.ProductSeasonFlatId) --It's okay if it occludes itself
				and (ProductId = inserted.ProductId)
		)
)
	BEGIN
		ROLLBACK TRANSACTION	
	    RAISERROR ('This table may not contain occluding seasons', 
            16, -- Severity.
            1 -- State.
        );
	END
END
GO