none
sql server 2019 - trigger su tracciato RRS feed

  • Domanda

  • è possibile creare un trigger che quando cambia il tracciato duimuna tabella in un db complesso  inserisce un record  in una aposita tabella del db stesso ?? [ i filologi sono pregati di astenersi ] 

    claudio cannella

    mercoledì 13 gennaio 2021 09:19

Risposte

  • Ciao,

    noi usiamo questo script: dopo aver creato un database di appoggio (DDLAudit), ti crea una tabella dove salvare le modifiche intercettate dal trigger DDL. Adattalo alle tue esigenze...

    --NOTE: Create database DDLAudit first!
    
    USE [DDLAudit]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Audit_Log](
    	[EventTime] [nvarchar](50) NULL,
    	[LoginName] [nvarchar](150) NULL,
    	[UserName] [nvarchar](150) NULL,
    	[DatabaseName] [nvarchar](100) NULL,
    	[SchemaName] [nvarchar](200) NULL,
    	[ObjectName] [nvarchar](200) NULL,
    	[ObjectType] [nvarchar](50) NULL,
    	[DDLCommand] [nvarchar](max) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    
    
    USE [master]
    GO
    
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    CREATE TRIGGER [Log_DDL_Events]
    
    
    ON ALL SERVER
    WITH EXECUTE AS 'sa' 
    
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
    	, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
    	, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
    	, CREATE_VIEW, ALTER_VIEW, DROP_VIEW
    AS 
    DECLARE       @eventInfo XML
    SET           @eventInfo = EVENTDATA()
    
    INSERT INTO DDLAudit..Audit_Log VALUES
    (
           CONVERT(VARCHAR(50),
                  @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),
           CONVERT(VARCHAR(150),
                  @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
           CONVERT(VARCHAR(150),
                  @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
           CONVERT(VARCHAR(100),
                  @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
           CONVERT(VARCHAR(200),
                  @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
           CONVERT(VARCHAR(200),
                  @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
           CONVERT(VARCHAR(50),
                  @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
           REPLACE(CONVERT(VARCHAR(MAX),
                  @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')), '
','')
    ) 
    GO
    
    ENABLE TRIGGER [Log_DDL_Events] ON ALL SERVER
    GO
    


    HTH,

    Cristiano Gasparotto, MCSE Data Management and Analytics

    You can find me working hard every day at Datamaze!

    Please Mark This As Answer if it solved your issue or Vote This As Helpful if it helps to solve your issue. Thank you!

    • Contrassegnato come risposta opaklaus mercoledì 13 gennaio 2021 20:48
    mercoledì 13 gennaio 2021 12:21

Tutte le risposte

  • Ciao,

    dalla versione 2016 hai a disposizione i triggers per statement DDL (CREATE, ALTER, ..) oltre che DML (INSERT, UPDATE, DELETE): i primi fanno al caso tuo.

    Nel documento linkato trovi esempi.

    Giorgio

    mercoledì 13 gennaio 2021 10:18
  • Ciao,

    noi usiamo questo script: dopo aver creato un database di appoggio (DDLAudit), ti crea una tabella dove salvare le modifiche intercettate dal trigger DDL. Adattalo alle tue esigenze...

    --NOTE: Create database DDLAudit first!
    
    USE [DDLAudit]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Audit_Log](
    	[EventTime] [nvarchar](50) NULL,
    	[LoginName] [nvarchar](150) NULL,
    	[UserName] [nvarchar](150) NULL,
    	[DatabaseName] [nvarchar](100) NULL,
    	[SchemaName] [nvarchar](200) NULL,
    	[ObjectName] [nvarchar](200) NULL,
    	[ObjectType] [nvarchar](50) NULL,
    	[DDLCommand] [nvarchar](max) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    
    
    USE [master]
    GO
    
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    CREATE TRIGGER [Log_DDL_Events]
    
    
    ON ALL SERVER
    WITH EXECUTE AS 'sa' 
    
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
    	, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
    	, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
    	, CREATE_VIEW, ALTER_VIEW, DROP_VIEW
    AS 
    DECLARE       @eventInfo XML
    SET           @eventInfo = EVENTDATA()
    
    INSERT INTO DDLAudit..Audit_Log VALUES
    (
           CONVERT(VARCHAR(50),
                  @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),
           CONVERT(VARCHAR(150),
                  @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
           CONVERT(VARCHAR(150),
                  @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
           CONVERT(VARCHAR(100),
                  @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
           CONVERT(VARCHAR(200),
                  @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
           CONVERT(VARCHAR(200),
                  @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
           CONVERT(VARCHAR(50),
                  @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
           REPLACE(CONVERT(VARCHAR(MAX),
                  @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')), '
','')
    ) 
    GO
    
    ENABLE TRIGGER [Log_DDL_Events] ON ALL SERVER
    GO
    


    HTH,

    Cristiano Gasparotto, MCSE Data Management and Analytics

    You can find me working hard every day at Datamaze!

    Please Mark This As Answer if it solved your issue or Vote This As Helpful if it helps to solve your issue. Thank you!

    • Contrassegnato come risposta opaklaus mercoledì 13 gennaio 2021 20:48
    mercoledì 13 gennaio 2021 12:21