1. 数据库触发器

指数据库相关对象的定义发生了变化所触发的事件。

1.1. 单个数据库

use master
go
CREATE TABLE [dbo].[ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate]  DEFAULT (getdate()),
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IP] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

go
create trigger backup_objects
on  database  --all server 所有数据库上的操作。database:当前数据库的操作。On用来指定作用域
for create_procedure, alter_procedure, drop_procedure,
    create_table, alter_table, drop_table,create_view, alter_view, drop_view,
    create_function, alter_function, drop_function
as
    set nocount on
    declare @data xml set @data = EVENTDATA()
    insert into master.dbo.changelog(databasename, eventtype, objectname, objecttype, sqlcommand, loginname,IP) 
    values( @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'), 
    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address') ) )
GO

1.2. 所有数据库

use master
go
CREATE TABLE [dbo].[ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate]  DEFAULT (getdate()),
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IP] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

go

create trigger backup_objects
on ALL SERVER --all server 所有数据库上的操作。database:当前数据库的操作。On用来指定作用域
for create_procedure, alter_procedure, drop_procedure,
    create_table, alter_table, drop_table,create_view, alter_view, drop_view,
    create_function, alter_function, drop_function
as
    set nocount on
    declare @data xml set @data = EVENTDATA()
    insert into master.dbo.changelog(databasename, eventtype, objectname, objecttype, sqlcommand, loginname,IP) 
    values( @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'), 
    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
    CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address') ) )
GO

1.3. 相关语句

--查看服务器级别的触发器
SELECT TOP 50 * FROM sys.server_triggers 

--查看服务器级别的触发器的定义
SELECT * FROM sys.server_sql_modules 

--查看激发触发器的数据库事件的信息
SELECT TOP 50 * FROM sys.server_trigger_events

--删除服务器上的DDL触发器
DROP TRIGGER backup_objects ON ALL SERVER

--失效DDL触发器
DISABLE TRIGGER backup_objects ON ALL SERVER

--获取有关数据库范围内的触发器的信息
SELECT * FROM sys.triggers 

--获取有关激发触发器的数据库事件的信息
SELECT * FROM sys.trigger_events 

--查看数据库范围内的触发器的定义
SELECT * FROM sys.sql_modules 

--删除当前数据库上的DDL触发器
DROP TRIGGER backup_objects ON DATABASE

1.4. 实例：存储过程变动追踪

1.4.1. 1.创建存储过程

use ebs2javaup_grade
go
create proc x 
as 
begin
    select * from ebs2java.dbo.mps_123123123
end

1.4.2. 2.修改存储过程

use ebs2javaup_grade
go
alter proc x 
as 
begin
    select * from ebs2java.dbo.mps_account
end

1.4.3. 3.查看日志

SELECT TOP 1000 [LogId]
  ,[DatabaseName]
  ,[EventType]
  ,[ObjectName]
  ,[ObjectType]
  ,[SqlCommand]
  ,[EventDate]
  ,[LoginName]
  ,[IP]
FROM master.[dbo].[ChangeLog]