CREATE TRIGGER [ddlDatabaseTriggerLog]
ON ALL SERVER
WITH EXECUTE AS ‘sa‘ ---根据实际情况选择
FOR DDL_DATABASE_LEVEL_EVENTS, CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE, CREATE_LOGIN
AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @LoginName sysname;
DECLARE @databasename sysname;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @LoginName
= @data.value(‘(/EVENT_INSTANCE/LoginName)[1]‘, ‘sysname‘);
SET @databasename
= @data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]‘, ‘sysname‘);
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 + ‘ - ‘ + @databasename + ‘.‘ + @schema + ‘.‘
+ @object;
ELSE
PRINT ‘ ‘ + @eventType + ‘ - ‘ + @databasename + ‘.‘ + @schema;
IF @eventType IS NULL
PRINT CONVERT(NVARCHAR(MAX), @data);
---写入的日志记录对应的库名是否正确
INSERT [azure_monitor].[dbo].[monitor_DatabaseLog]
( [PostTime],
[DatabaseUser],
[LoginName],
[Event],
[databasename],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
( GETDATE(),
CONVERT(sysname, CURRENT_USER),
CONVERT(sysname, @LoginName),
@eventType,
CONVERT(sysname, @databasename),
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(max)‘),
@data
);
END;
GO
来源: http://www.bubuko.com/infodetail-2251954.html