SQL Server 預存程序版本控制-DDL觸發器

標籤：

預存程序版本控制-DDL觸發器–參考：預存程序版本控制http://enjoyasp.net/?p=2431 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]  ALTER trigger backup_objectson 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_functionasset nocount ondeclare @data xml set @data = EVENTDATA()insert into BRM_TEST.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–DDL觸發器http://msdn.microsoft.com/zh-cn/library/ms175941.aspx –查看伺服器層級的觸發器 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

 

SQL Server 預存程序版本控制-DDL觸發器