if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_movefile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_movefile] GO/*--移动服务器上的文件不借助 xp_cmdshell ,因为这个在大多数时候都被禁用了--邹建 2004.08(引用请保留此信息)--*//*--调用示例exec p_movefile 'd:\aa.txt','c:\' --*/ create proc p_movefile @s_file varchar(1000), --源文件 @d_file varchar(1000) --目标文件 as declare @err int,@src varchar(255),@desc varchar(255) declare @obj intexec @err=sp_oacreate 'Scripting.FileSystemObject',@obj out if @err<>0 goto lberrexec @err=sp_oamethod @obj,'MoveFile',null,@s_file,@d_file if @err<>0 goto lberrexec @err=sp_oadestroy @obj returnlberr: exec sp_oageterrorinfo 0,@src out,@desc out select cast(@err as varbinary(4)) as 错误号 ,@src as 错误源,@desc as 错误描述 go
/*创建测试表,及插入测试表文件路径*/ create table t(id int identity(1,1),msg nvarchar(100)) insert into t select 'D:\test\test1.txt' insert into t select 'D:\test\test2.txt' go /*创建触发器*/ create trigger trigger_test on t for delete as declare @a nvarchar(100) select @a=msg from deleted exec ('master..xp_cmdshell''del '+@a+''',no_output') go /*测试,删除D:\test\test1.txt记录并删除相应文件*/ delete t where msg='D:\test\test1.txt' /*删除测试表*/ drop table t
/*创建测试表,及插入测试表文件路径*/ create table t(id int identity(1,1),msg nvarchar(100)) insert into t select 'D:\test\test1.txt' insert into t select 'D:\test\test2.txt' go /*创建触发器*/ create trigger trigger_test on t for delete as declare @a nvarchar(100) select @a=msg from deleted exec ('master..xp_cmdshell''del '+@a+''',no_output') go /*测试,删除D:\test\test1.txt记录并删除相应文件*/ delete t where msg='D:\test\test1.txt' exec master..xp_cmdshell 'del d:\test\test1.txt' /*删除测试表*/ drop table t
感谢leeboyan(宝宝),但是现在有一个新的问题: select @a=msg from deleted这条语句只能取得最后一条被删除记录的内容,可是,当我整批删除时,其它条记录相关的触发器就不会被执行,怎么办? 如我执行:delete t where msg='D:\test\test1.txt' or 'D:\test\test2.txt' 则只有test1文件被删除
下例将当前目录内容写入当前服务器目录下名为 dir_out.txt 的文件中。DECLARE @cmd sysname, @var sysname
SET @var = 'dir /p'
SET @cmd = 'echo ' + @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd
drop procedure [dbo].[p_movefile]
GO/*--移动服务器上的文件不借助 xp_cmdshell ,因为这个在大多数时候都被禁用了--邹建 2004.08(引用请保留此信息)--*//*--调用示例exec p_movefile 'd:\aa.txt','c:\'
--*/
create proc p_movefile
@s_file varchar(1000), --源文件
@d_file varchar(1000) --目标文件
as
declare @err int,@src varchar(255),@desc varchar(255)
declare @obj intexec @err=sp_oacreate 'Scripting.FileSystemObject',@obj out
if @err<>0 goto lberrexec @err=sp_oamethod @obj,'MoveFile',null,@s_file,@d_file
if @err<>0 goto lberrexec @err=sp_oadestroy @obj
returnlberr:
exec sp_oageterrorinfo 0,@src out,@desc out
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
go
create table t(id int identity(1,1),msg nvarchar(100))
insert into t select 'D:\test\test1.txt'
insert into t select 'D:\test\test2.txt'
go
/*创建触发器*/
create trigger trigger_test on t
for delete
as
declare @a nvarchar(100)
select @a=msg from deleted
exec ('master..xp_cmdshell''del '+@a+''',no_output')
go
/*测试,删除D:\test\test1.txt记录并删除相应文件*/
delete t where msg='D:\test\test1.txt'
/*删除测试表*/
drop table t
create table t(id int identity(1,1),msg nvarchar(100))
insert into t select 'D:\test\test1.txt'
insert into t select 'D:\test\test2.txt'
go
/*创建触发器*/
create trigger trigger_test on t
for delete
as
declare @a nvarchar(100)
select @a=msg from deleted
exec ('master..xp_cmdshell''del '+@a+''',no_output')
go
/*测试,删除D:\test\test1.txt记录并删除相应文件*/
delete t where msg='D:\test\test1.txt'
exec master..xp_cmdshell 'del d:\test\test1.txt'
/*删除测试表*/
drop table t
select @a=msg from deleted这条语句只能取得最后一条被删除记录的内容,可是,当我整批删除时,其它条记录相关的触发器就不会被执行,怎么办?
如我执行:delete t where msg='D:\test\test1.txt' or 'D:\test\test2.txt' 则只有test1文件被删除