SQL SERVER 2005 : HISTORISER LES PROCÉDURES STOCKÉES D'UNE BASE DE DONNÉES

CREATE TRIGGER [TrackSpChange] ON DATABASE
    FOR ALTER_PROCEDURE
AS
    BEGIN
	
		SET NOCOUNT ON

        DECLARE @sql nvarchar(max)
        DECLARE @event xml
        DECLARE @id int
	
        SET @event = eventdata()
        SET @id = OBJECT_ID(@event.value('/EVENT_INSTANCE[1]/ObjectName[1]',
                                         'sysname'), 'P')	
        SET @sql = @event.value('/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1]',
                                'nvarchar(max)')

        ROLLBACK
        BEGIN TRANSACTION

        IF OBJECT_ID('dbo.SpHisto', 'U') IS NULL 
            SELECT TOP ( 0 )
                    *,
                    GETDATE() AS DateModified
            INTO    dbo.SpHisto
            FROM    sys.sql_modules

        INSERT  INTO dbo.SpHisto
                SELECT  *,
                        GETDATE() AS DateModified
                FROM    sys.sql_modules
                WHERE   object_id = @id

        EXEC ( @sql )

		SET NOCOUNT OFF

    END
GO