EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@AuditOperation	nvarchar	IN

Definition

CREATE PROCEDURE p_DDLTriggerGenerator
/*Version=07.08.19*/
@AuditOperation NVARCHAR(38) = NULL /*C=CREATE DDL TRIGGER, D=DROP DDL TRIGGER*/
AS
DECLARE @SQLCommand NVARCHAR(MAX),
        @Owner NVARCHAR(30)
SELECT @Owner = AUDITSCHEMANAME FROM ZAUDITCONFIG
IF @AuditOperation = 'C'
BEGIN
IF EXISTS (SELECT 1 FROM sys.triggers  WHERE NAME ='t_DDLAudit')
SET @SQLCommand ='DROP TRIGGER t_DDLAudit ON DATABASE'
EXEC sp_executesql @SQLCommand
SET @SQLCommand ='CREATE TRIGGER t_DDLAudit' + CHAR(10)
SET @SQLCommand += 'ON DATABASE' + CHAR(10)
SET @SQLCommand += 'FOR DDL_DATABASE_LEVEL_EVENTS' + CHAR(10)
SET @SQLCommand += 'AS' + CHAR(10)
SET @SQLCommand += 'DECLARE @rowsaffected int = @@ROWCOUNT' + CHAR(10)
SET @SQLCommand += '        ,@eventData xml = eventdata()' + CHAR(10)
SET @SQLCommand += '        ,@ddlevent nvarchar(max)' + CHAR(10)
SET @SQLCommand += '        ,@ddl nvarchar(max)' + CHAR(10)
SET @SQLCommand += '        ,@tableName nvarchar(max)' + CHAR(10)
SET @SQLCommand += '        ,@RunningSchema varchar(64)' + CHAR(10)
SET @SQLCommand += '        ,@ScopeLog int = null' + CHAR(10) + CHAR(10)
set @SQLCommand += 'SET CONCAT_NULL_YIELDS_NULL ON' + CHAR(10)
SET @SQLCommand += 'SET NOCOUNT ON SET ANSI_PADDING ON SET ARITHABORT ON' + CHAR(10) + CHAR(10)
SET @SQLCommand += 'SET @ddlevent = CONVERT(nvarchar(max),@EventData.value(''(/EVENT_INSTANCE/EventType)[1]'', ''nvarchar(96)''))' + CHAR(10)
SET @SQLCommand += 'SET @ddl = CONVERT(nvarchar(max), @eventData.value(''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'',''nvarchar(max)'')) ' + CHAR(10)
SET @SQLCommand += 'SET @tableName = CONVERT(nvarchar(max), @eventData.value(''(/EVENT_INSTANCE/ObjectName)[1]'',''nvarchar(100)''))' + CHAR(10)
SET @SQLCommand += 'SET @RunningSchema = CONVERT(nvarchar(max), @eventData.value(''(/EVENT_INSTANCE/SchemaName)[1]'',''nvarchar(100)''))' + CHAR(10) + CHAR(10)
SET @SQLCommand += '--Salva dados do evento' + CHAR(10)
SET @SQLCommand += 'IF @ddlevent NOT IN (''CREATE_STATISTICS'',''UPDATE_STATISTICS'',''ALTER_INDEX'')' + CHAR(10)
SET @SQLCommand += 'INSERT INTO [' + @Owner + '].[ZAUDITSCHEMAEVENTS](USERNAME, LOGINNAME, HOSTNAME, APPLICATIONNAME, APPLICATIONMODULE, MACHINEIP, EVENTDATE, EVENTTYPE, [STATEMENT], SCHEMANAME, OBJECTNAME)' + CHAR(10)
SET @SQLCommand += 'SELECT C.username, c.netuser, C.hostname, app_name(), C.processname, C.ipaddress, getdate(), @ddlevent, @ddl, @RunningSchema, @tableName from dbo.fn_GetContext() c'
EXEC sp_executesql @SQLCommand
END
IF @AuditOperation = 'D'
BEGIN
IF EXISTS (SELECT 1 FROM sys.triggers  WHERE NAME ='t_DDLAudit')
SET @SQLCommand ='DROP TRIGGER t_DDLAudit ON DATABASE'
EXEC sp_executesql @SQLCommand
END