CREATE TABLE [DatabaseLog] (
ID INT PRIMARY KEY IDENTITY(1,1),
Command NVARCHAR(1000),
PostTime NVARCHAR(24),
[Event] NVARCHAR(100),
[Schema] NVARCHAR(100),
LoginName NVARCHAR(100),
XmlEvent xml,
[object] NVARCHAR(100),
[login] NVARCHAR(100),
hostname NVARCHAR(100),
spid NVARCHAR(24))
CREATE TRIGGER [ddlDatabaseTriggerLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
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')
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
DECLARE @spid NVARCHAR(6)
SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)')
SET @hostname = HOST_NAME()
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')
IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
INSERT [dbo].[DatabaseLog]
(
[PostTime],
[LoginName],
[Event],
[Schema],
[Object],
[Command],
[XmlEvent],
[login],
[hostname],
[spid]
)
VALUES
(
GETDATE(),
CONVERT(sysname, CURRENT_USER),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
@data,
@loginname,
@hostname,
@spid
);
END;
GO
CREATE TABLE dbo.Test(col INT)
DROP TABLE dbo.Test
SELECT * FROM [DatabaseLog]
T-SQL - Using Trigger to monitor DDL changes
最新推荐文章于 2022-05-17 12:00:39 发布