SQL Server DDL触发器记录表结构修改

创建 DatabaseLog 表和 DLL 触发器 DDL_DatabaseLog，记录修改表结构操作

USE mjkq;
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'DatabaseLog')
BEGIN
    DROP TABLE  [dbo].[DatabaseLog];
END;
GO
CREATE TABLE [dbo].[DatabaseLog](
    [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [ServerName] [sysname] NOT NULL,
    [LoginName] [sysname] NOT NULL,
    [ClientIp] varchar(20) NULL,
    [DatabaseUser] [sysname] NOT NULL,
    [DatabaseName] [sysname] NOT NULL,
    [Schema] [sysname] NULL,
    [Object] [sysname] NULL,
    [TSQL] [nvarchar](max) NOT NULL,
    [Event] [sysname] NOT NULL,
    [XmlEvent] [xml] NOT NULL,
 CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED 
(
    [DatabaseLogID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
-- 删除现有触发器
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'DDL_DatabaseLog')
BEGIN
    DROP TRIGGER [DDL_DatabaseLog] ON DATABASE;
END;
GO
-- 创建触发器
CREATE TRIGGER [DDL_DatabaseLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
    SET NOCOUNT ON;
    
    -- 声明变量
    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;
    DECLARE @clientip varchar(20);

    -- 获取事件数据
    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');
    SELECT @clientip = client_net_address from sys.dm_exec_connections WHERE session_id  = @@SPID;
    
    -- 打印调试信息
    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;
        
    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);
    
    -- 插入数据库日志
    INSERT [mjkq].[dbo].[DatabaseLog](
        [PostTime], 
        [ServerName], 
        [LoginName], 
        [ClientIp],
        [DatabaseUser], 
        [DatabaseName],
        [Schema], 
        [Object], 
        [TSQL], 
        [Event], 
        [XmlEvent])
    VALUES(
        GETDATE(), 
        @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'), 
        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'), 
        @clientip,
        CONVERT(sysname, CURRENT_USER),
        @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname'),  
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
        @eventType,  
        @data
    );
END;

GO
CREATE TABLE TestTable(a int)

GO
DROP TABLE TestTable;

SELECT * FROM [DatabaseLog];
GO

创建DDL DDL_TableTrigger 触发器，禁止修改或者删除数据表

--创建DDL触发器：禁止修改或者删除数据表
CREATE TRIGGER DDL_TableTrigger
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
   PRINT '对不起，您不能对数据表进行操作，请联系DBA'
   ROLLBACK ;

---

SQL Server DDL触发器运用
DLL触发器