为数据库生成表的改动日志

来源：互联网发布：凯立德优化内存编辑：程序博客网时间：2024/06/04 23:08

IF OBJECT_ID('dbo.AuditDDLEvents') IS NOT NULL
DROP TABLE dbo.AuditDDLEvents;
GO

CREATE TABLE dbo.AuditDDLEvents
(
lsn              INT      NOT NULL IDENTITY,
posttime         DATETIME NOT NULL,
eventtype        sysname NOT NULL,
loginname        sysname NOT NULL,
schemaname       sysname NOT NULL,
objectname       sysname NOT NULL,
targetobjectname sysname NULL,
eventdata        XML      NOT NULL,
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(lsn)
);
GO

CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS

DECLARE @eventdata AS XML;
SET @eventdata = eventdata();

INSERT INTO dbo.AuditDDLEvents(
posttime, eventtype, loginname, schemaname,
objectname, targetobjectname, eventdata)
VALUES(
    @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]',         'VARCHAR(23)'),
    @eventdata.value('(/EVENT_INSTANCE/EventType)[1]',        'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]',        'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]',       'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]',       'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'),
    @eventdata);
GO

-- Test trigger trg_audit_ddl_events
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
ALTER TABLE dbo.T1 ADD col2 INT NULL;
ALTER TABLE dbo.T1 ALTER COLUMN col2 INT NOT NULL;
CREATE NONCLUSTERED INDEX idx1 ON dbo.T1(col2);
GO

SELECT * FROM dbo.AuditDDLEvents;
GO

0 0