存储过程版本控制-DDL触发器

 参考:http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

USE CedarLog
GO

CREATE TABLE [dbo].[ChangeLog](
	[LogId] [INT] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [VARCHAR](256) NOT NULL,
	[EventType] [VARCHAR](50) NOT NULL,
	[ObjectName] [VARCHAR](256) NOT NULL,
	[ObjectType] [VARCHAR](25) NOT NULL,
	[SqlCommand] [NVARCHAR](MAX) NOT NULL,
	[EventDate] [DATETIME] NOT NULL CONSTRAINT [DF_EventsLog_EventDate]  DEFAULT (GETDATE()),
	[LoginName] [VARCHAR](256) NOT NULL,
	[IP] [VARCHAR](50) NOT NULL,
 CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED
(
	[LogId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

CREATE TRIGGER [backup_objects]
ON ALL SERVER
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()
IF @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)') NOT LIKE 'SqlQuery%'
BEGIN
	INSERT INTO CedarLog.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') ) )
END
GO

ENABLE TRIGGER [backup_objects] ON ALL SERVER
GO

--查看服务器级别的触发器
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