建立跟踪的相关表
--触发事件记录表
CREATE TABLE [VER_EVENT](
[SN] bigint identity(1,1),
[LoginName] SYSNAME NULL,
[PostTime] [datetime] NULL,
[DatabaseName] SYSNAME NULL,
[EventType] SYSNAME NULL,
[ObjectType] SYSNAME NULL,
[ObjectName] SYSNAME NULL,
[CommandText] [nvarchar](max) NULL
) ON [PRIMARY]
GO
--版本控制表
CREATE TABLE [VER_CONTROL](
[SN] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NULL,
[ObjectType] [sysname] NULL,
[ObjectName] [sysname] NULL,
[Version] [int] NULL,
[SN_EVENT] [bigint] NULL
) ON [PRIMARY]
建立触发器
/************************************************************************
-- 功 能：数据库的对象版本跟踪系统
-- 日 期：2008-10-09
-- 作 者：
-- 参 数：
-- 返 回 值：
-- 说 明：有两个相关的表
************************************************************************/
--CREATE TRIGGER VERSION_CONTROL
ALTER TRIGGER [VERSION_CONTROL]
ON DATABASE
FOR
CREATE_TABLE,ALTER_TABLE,DROP_TABLE,
CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE,
CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @ObjectName SYSNAME
DECLARE @EventType SYSNAME
DECLARE @DatabaseName SYSNAME
DECLARE @ObjectType SYSNAME
DECLARE @CommandText NVARCHAR(MAX)
DECLARE @Ver INT
DECLARE @SN INT
SET @Ver = NULL
SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')
SET @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','sysname')
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')
SET @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','sysname')
SET @CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
INSERT INTO VER_EVENT([LoginName],[PostTime],[DatabaseName],[EventType],[ObjectType],[ObjectName],[CommandText])
SELECT
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','sysname') as LoginName,
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') as PostTime,
@DatabaseName,@EventType,@ObjectType,@ObjectName,@CommandText
SET @SN = @@IDENTITY
SELECT @Ver = [Version]
FROM VER_CONTROL
WHERE [DatabaseName] = @DatabaseName AND [ObjectType] = @ObjectType AND [ObjectName] = @ObjectName
IF @EventType LIKE 'CREATE%' AND @ObjectName NOT LIKE 'T%'
BEGIN
IF @Ver IS NULL
INSERT INTO [VER_CONTROL]([DatabaseName],[ObjectType],[ObjectName],[Version],[SN_EVENT])
SELECT @DatabaseName,@ObjectType,@ObjectName,10001,0
ELSE
UPDATE VER_CONTROL
SET [Version] = @Ver + 10000,[SN_EVENT] = @SN
WHERE [DatabaseName] = @DatabaseName AND [ObjectType] = @ObjectType AND [ObjectName] = @ObjectName
END
IF @EventType LIKE 'ALTER%' AND @ObjectName NOT LIKE 'T%'
BEGIN
IF @Ver IS NULL
INSERT INTO [VER_CONTROL]([DatabaseName],[ObjectType],[ObjectName],[Version],[SN_EVENT])
SELECT @DatabaseName,@ObjectType,@ObjectName,10001,0
ELSE
UPDATE VER_CONTROL
SET [Version] = @Ver + 1,[SN_EVENT] = @SN
WHERE [DatabaseName] = @DatabaseName AND [ObjectType] = @ObjectType AND [ObjectName] = @ObjectName
END
IF @EventType LIKE 'DROP%' AND @ObjectName NOT LIKE 'T%'
BEGIN
IF @Ver IS NULL
INSERT INTO [VER_CONTROL]([DatabaseName],[ObjectType],[ObjectName],[Version],[SN_EVENT])
SELECT @DatabaseName,@ObjectType,@ObjectName,-10001,0
ELSE
UPDATE VER_CONTROL
SET [Version] = @Ver * (-1),[SN_EVENT] = @SN
WHERE [DatabaseName] = @DatabaseName AND [ObjectType] = @ObjectType AND [ObjectName] = @ObjectName
RETURN
END