SQL Server 获取表本次更新了哪些字段以及新旧值

Posted on by

经查阅资料以及个人实际业务需求，编写了一个（在触发器使用）可以定位本次修改的id、修改的字段、字段新旧值，形成一个表格。
存储过程就传入一个参数：表名即可，无需带dbo等任何前缀。
update触发器中必须需将inserted和deleted做成临时表，请按照如下编写：

Select * into #deleted from deleted
Select * into #inserted from inserted
exec p_GetDiffColumns '产品表'

效果如下：

存储过程代码如下：

USE [test]
GO

ALTER proc [dbo].[p_GetDiffColumns]
(
	@TableName varchar(100)
)
as
begin
	Declare @Name nvarchar(Max)
	Declare @SQLTmp nvarchar(Max)
	Declare @RowValue nvarchar(Max)= null
	Declare @tmpTableName nvarchar(Max)
	set @tmpTableName = 'T' +
	replace(
		replace(
			replace(
				replace(
					CONVERT(nvarchar(50), GETDATE(), 21)
				, '-', '')
			, ':', '')
		, ' ', '')
	, '.', '')
	
	exec(N'select 
			cast('''' as nvarchar(max)) as ID, 
			cast('''' as nvarchar(max)) as ColName, 
			cast('''' as nvarchar(max)) as OldValue, 
			cast('''' as nvarchar(max)) as NewValue 
			into ' + @tmpTableName)

	Declare CheckColummValue Cursor for 
		Select name from sys.columns where OBJECT_ID=OBJECT_ID(@TableName)
	Open CheckColummValue
	FETCH NEXT FROM CheckColummValue INTO @Name
	WHILE @@FETCH_STATUS = 0
	BEGIN
	Set @SQLTmp=N'
	Insert into ' + @tmpTableName + ' (ID, ColName,OldValue, NewValue)
	Select a.ID, '''+@Name+''',a.'+@Name+',b.'+@Name+' From #deleted a, #inserted b Where a.id=b.id and a.'+@Name+'<>'+'b.'+@Name
	exec @RowValue=sp_executesql @SQLTmp
	FETCH NEXT FROM CheckColummValue INTO @Name
	End
	close CheckColummValue
	deallocate CheckColummValue 
	
	
	exec(N'select * from ' + @tmpTableName + ' where ColName != '''' ')
	exec(N'drop table ' + @tmpTableName)
	return
end
GO

bat 批处理实现重启打印服务

Posted on by

代码如下，就是使用net去关闭然后打开spooler服务，在调用net之前，需要先获取管理员权限，所以上面的代码就是不需要用户右键–管理员身份运行，bat自动转换为管理员用户运行。

将代码保存成记事本，后缀改为bat双击即可运行。

@echo off
%1 mshta vbscript:CreateObject("Shell.Application").ShellExecute("cmd.exe","/c %~s0 ::","","runas",1)(window.close)&&exit
cd /d "%~dp0"

net stop spooler 
net start spooler 
pause

Uwamp windows定时重启bat处理

Posted on by

创建一个bat文件，里面写入下面代码即可，注意最后一行启动UwAmpUAC时改为自己真实文件存放路径。

taskkill /f /t /im httpd.exe
taskkill /f /t /im UwAmp.exe
D:\UwAmp\UwAmpUAC.exe