Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_syspolicy_update_ddl_trigger

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_syspolicy_update_ddl_trigger
AS
    IF (msdb.dbo.fn_syspolicy_is_automation_enabled() = 0)
    BEGIN
        RETURN;
    END

	DECLARE @event_list nvarchar(max)
	SET @event_list = N''

	CREATE TABLE #temp_events (event_name sysname COLLATE database_default)
	-- pick up all the events on policies that have to be enforced
	INSERT #temp_events
		SELECT DISTINCT fe.event_name COLLATE database_default
			FROM msdb.dbo.syspolicy_policies pi
			INNER JOIN msdb.dbo.syspolicy_conditions_internal c ON pi.condition_id = c.condition_id
			INNER JOIN msdb.dbo.syspolicy_facet_events fe ON c.facet_id = fe.management_facet_id
			INNER JOIN sys.event_notification_event_types ent ON fe.event_name = ent.type_name COLLATE database_default
			WHERE (pi.execution_mode & 1) = 1 AND pi.is_enabled = 1 AND ent.type < 1000 AND
				EXISTS (SELECT * FROM msdb.dbo.syspolicy_object_sets_internal os WHERE os.object_set_id = pi.object_set_id)

	-- create a cursor so that we can transform the table into a list
	DECLARE event_cursor CURSOR LOCAL FOR
		SELECT event_name FROM #temp_events
	
	DECLARE @event_name sysname
	OPEN event_cursor
	FETCH NEXT FROM event_cursor INTO @event_name
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @event_list = @event_list + @event_name + ','	
		FETCH NEXT FROM event_cursor INTO @event_name
	END

	CLOSE event_cursor
	DEALLOCATE event_cursor

	DROP TABLE #temp_events

	-- take out the last comma
	IF( LEN(@event_list) > 0)
		SET @event_list = SUBSTRING(@event_list, 1, LEN(@event_list)-1)

	-- no more events to read means we drop the trigger
	IF(LEN(@event_list)=0)
	BEGIN
		IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = N'syspolicy_server_trigger')
			DROP TRIGGER [syspolicy_server_trigger] ON ALL SERVER
		RETURN (0)
	END
		
	DECLARE @mode nvarchar(20)
	IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = N'syspolicy_server_trigger')
		SET @mode = N'	ALTER '
	ELSE
		SET @mode = N'	CREATE '
		
	DECLARE @statement nvarchar(max)
	SET @statement  = @mode + N'TRIGGER [syspolicy_server_trigger] ON ALL SERVER
	WITH EXECUTE AS ''##MS_PolicyEventProcessingLogin##''
	FOR ' + @event_list + '
	AS
	BEGIN
		DECLARE @event_data xml
		SELECT @event_data = EVENTDATA()
		EXEC [msdb].[dbo].[sp_syspolicy_dispatch_event] @event_data = @event_data, @synchronous = 1
	END'


	EXEC sp_executesql @statement

	RETURN (0)

 
Last revision 2008RTM
See also

  sp_syspolicy_set_config_enabled (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash