SQL2005 库级触发器的运用

/*---------------------------------

版本号： 2009-5-20
作 者 ： 叮叮猫
起作用 只针对对数据的内表的增加 删除修改
但是当你对数据库内数据的操作 是不能进行捕获的
----------------------------*/
USE testDB
GO
USE [testDB]
GO
create table [DatabaseLog] --创建一张表进行查询
(
 [PostTime] datetime,
  [DatabaseUser] xml,
     [Event] xml,
     [Schema] xml,
     [Object] xml,
     [TSQL] xml,
     [XmlEvent] xml
)
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
go
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')

    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],
        [DatabaseUser],
        [Event],
        [Schema],
        [Object],
        [TSQL],
        [XmlEvent]
        )
    VALUES
        (
        GETDATE(),
        CONVERT(sysname, CURRENT_USER),
        CONVERT(sysname,@eventType),
        CONVERT(sysname, @schema),
        CONVERT(sysname, @object),
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
        @data
        );
END;

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

------------------------测试-------------------------------
create table testtabel
(
 id int
)
insert into testtabel values(111)
update testtabel set id=333
drop table testtabel
select * from [DatabaseLog]