SqlServer Sql数据库触发器记录对象修改记录
2017-09-01Sql Server站长10959°c
A+ A-/* 目的: 1.总是感觉这次看到的过程 或者触发器 和上次不一样了,有了这个功能就可以看到修改记录了 2.记录下修改日志 对恢复成原来版本帮助应该也有 功能描述 :该功能为数据库触发器,可以在对数据库对象进行编辑是保存修改语句 EXEC sys.sp_configure N'show advanced options', 1 go RECONFIGURE WITH OVERRIDE go EXEC sys.sp_configure 'Database Mail xps', 1; go RECONFIGURE WITH OVERRIDE; go by hydee.qxw 20160801 */ set quoted_identifier on go if exists (select * from sys.triggers where parent_class_desc = 'DATABASE' and name = N'TR_db_log') disable trigger [TR_db_log] on database go if exists (select * from sys.triggers where parent_class_desc = 'DATABASE' and name = N'TR_db_log')drop trigger [TR_db_log] on database go if object_id('d_db_log') is not null drop table d_db_log create table d_db_log(LogID int identity(1,1) not null, DB_User varchar(100),LoginName varchar(100),EventType varchar(100), SQLString varchar(max),ChangeTime datetime) go /* EVENTDATA() 数据xml样式 <EVENT_INSTANCE><EventType>ALTER_TABLE</EventType><PostTime>2016-08-01T11:47:48.017</PostTime><SPID>54</SPID><ServerName>XW-PC</ServerName><LoginName>XW-PC\XW</LoginName><UserName>dbo</UserName><DatabaseName>bzk</DatabaseName><SchemaName>dbo</SchemaName><O */ CREATE TRIGGER TR_db_log ON DATABASE FOR CREATE_TABLE, ALTER_TABLE ,DROP_TABLE, --table CREATE_CONTRACT,DROP_CONTRACT,--constraint CREATE_INDEX,ALTER_INDEX,DROP_INDEX,--index CREATE_VIEW,ALTER_VIEW,DROP_VIEW,--view CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE,--procedure CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,--function CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER --trigger AS DECLARE @data XML SET @data = EVENTDATA() INSERT d_db_log (DB_User,EventType,LoginName,SQLString,ChangeTime) VALUES (CONVERT(VARCHAR(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(100)'), @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'VARCHAR(8000)'), GETDATE() ) go
标签:Sql Server