Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
–参考:存储过程版本控制
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