Datafly.pro

Le code suivant permet d'enregistrer dans une table les modifications (qui, quand, quoi...) :

-- Crée la table de log
CREATE TABLE [dbo].[ViewsLog](
        [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
        [PostTime] [datetime] NOT NULL,
        [DatabaseUser] [sysname] NOT NULL,
        [Event] [sysname] NOT NULL,
        [Schema] [sysname] NULL,
        [Object] [sysname] NULL,
        [TSQL] [nvarchar](max) NOT NULL,
        [XmlEvent] [xml] NOT NULL,
 CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED 
(
        [DatabaseLogID] ASC
)) ON [PRIMARY]
GO

-- Crée le déclencheur DDL
CREATE TRIGGER [ddlViewsTriggerLog] 
ON DATABASE 
FOR CREATE_VIEW,ALTER_VIEW,DROP_VIEW 
AS 
BEGIN
    SET NOCOUNT ON

    DECLARE @data XML
    DECLARE @schema sysname
    DECLARE @object sysname
    DECLARE @eventType sysname

    SET @data = EVENTDATA()
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname')
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);

    INSERT [dbo].[ViewsLog] 
        (
        [PostTime], 
        [DatabaseUser], 
        [Event], 
        [Schema], 
        [Object], 
        [TSQL], 
        [XmlEvent]
        ) 
    VALUES 
        (
        GETDATE(), 
        CONVERT(sysname, SYSTEM_USER), 
        @eventType, 
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
        @data
        );
END

GO