SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Christian Terhart
-- Create date: 2013-02-18
-- Description:	Trigger Report Scheduled Task
-- =============================================
ALTER PROCEDURE dbo.usp_report_event_trigger
	@report_name nvarchar(425) = NULL,
	@report_path nvarchar(425) = '',
	@report_action int = -1 
	/*	ReportAction	EventType
		3				CacheInvalidateSchedule
		4				TimedSubscription
		5				RefreshCache
	*/
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exec_command nvarchar(4000) = '';
	
	SELECT 
		@exec_command = 
			@exec_command + N'EXECUTE ReportServer.dbo.AddEvent @EventType=''' + sc.EventType + ''', @EventData='''+CONVERT(nvarchar(36), UPPER(rs.SubscriptionID))+N'''; '
	FROM dbo.Catalog AS c
	INNER JOIN dbo.ReportSchedule	AS rs ON (c.ItemID = rs.ReportID AND rs.SubscriptionID IS NOT NULL)
	INNER JOIN dbo.Schedule			AS sc ON (rs.ScheduleID = sc.ScheduleID)
	WHERE c.Name = @report_name 
	AND (c.Path = @report_path OR @report_path = '')
	AND (rs.ReportAction = @report_action OR @report_action = -1)
	
	EXECUTE sp_executesql  @exec_command 
END
GO