使用数据库级触发器记录数据库更改历史

 

--//

CREATE

go

USE

--

CREATE

 

DDLHistory

(

 

[Login] 

NVARCHAR(100),

[Action]

NVARCHAR(100),

[Schema] 

NVARCHAR(100 ),

Object 

TSQL 

)

go

--

CREATE

 

ON

DATABASE

FOR

DDL_DATABASE_LEVEL_EVENTS

AS

BEGIN

 

DECLARE @data XML

SET @data = eventdata ()

 

INSERT DDLHistory  ([Time], [Login], [Action], [Schema], [Object], TSQL)

VALUES

(GETDATE(), @data.value('data(//LoginName)[1]', 'NVARCHAR(2000)'),

@data

@data.value('data(//SchemaName)[1]', 'NVARCHAR(2000)' ),

.value('data(//EventType)[1]', 'NVARCHAR(2000)'),

@data

@data

END

go

--

CREATE

TestTable

(

 

)

go

ALTER

ADD

go

CREATE

AS

 

SELECT * FROM TestTable

go

CREATE

AS

 

SELECT * FROM TestTable

go

ALTER

AS

SELECT

go

 

ALTER

AS

 

 SELECT

END

go

--

SELECT

FROM  DDLHistory

use

go

--

---drop database Testdb

master ;[Time], [Action], [Schema], [Object], TSQL BEGINB FROM TestTablePROC prcTestViewa FROM TestTable VIEW vwTestViewPROC prcTestViewVIEW vwTestViewB INTTABLE TestTableA INT TABLE .value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(2000)') ).value('data(//ObjectName)[1]', 'NVARCHAR(2000)' ), TRIGGER trDDLHistoryRecorderNVARCHAR(2000 ) NVARCHAR(100 ),[Time] DATETIME, TABLE Testdb DATABASE Testdb ;