34,576
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')
DISABLE TRIGGER [DDLTriggertTrace] ON DATABASE
GO
/****** Object: DdlTrigger [DDLTriggertTrace] Script Date: 12/21/2012 14:16:36 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')DROP TRIGGER [DDLTriggertTrace] ON DATABASE
GO
/****** Object: DdlTrigger [DDLTriggertTrace] Script Date: 12/21/2012 14:16:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDLTriggertTrace] ON DATABASE
--捕获存储过程、视图、表的创建、修改、删除动作
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_VIEW,
ALTER_VIEW, DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @EventData XML = EVENTDATA() ;--返回有关服务器或数据库事件的信息,以XML格式保存。
DECLARE @ip VARCHAR(32) = ( SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
) ;
INSERT AuditDB.dbo.DDLEvents
( EventType ,
EventDDL ,
EventXML ,
DatabaseName ,
SchemaName ,
ObjectName ,
HostName ,
IPAddress ,
ProgramName ,
LoginName
)
SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]',
'NVARCHAR(100)') ,
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'NVARCHAR(MAX)') ,
@EventData ,
DB_NAME() ,
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',
'NVARCHAR(255)') ,
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',
'NVARCHAR(255)') ,
HOST_NAME() ,
@ip ,
PROGRAM_NAME() ,
SUSER_SNAME() ;
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
存放的表:
/****** Object: Table [dbo].[DDLEvents] Script Date: 12/21/2012 14:17:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DDLEvents](
[EventDate] [datetime] NOT NULL,
[EventType] [nvarchar](64) NULL,
[EventDDL] [nvarchar](max) NULL,
[EventXML] [xml] NULL,
[DatabaseName] [nvarchar](255) NULL,
[SchemaName] [nvarchar](255) NULL,
[ObjectName] [nvarchar](255) NULL,
[HostName] [varchar](64) NULL,
[IPAddress] [varchar](32) NULL,
[ProgramName] [nvarchar](255) NULL,
[LoginName] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DDLEvents] ADD DEFAULT (getdate()) FOR [EventDate]
GO