MsSql综合技巧:在Trigger里面运用动态sql和exec configuration地疑难！[库库中文网]-数据库类教程 QQGB.com

SQL code
IF OBJECT_ID ('UTR_ExportPL', 'TR') IS NOT NULL DROP TRIGGER UTR_ExportPL Go CREATE TRIGGER UTR_ExportPL ON sdbc1..OR033100 AFTER UPDATE AS EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE -- Exec Export Query SELECT * INTO #inserted FROM inserted declare @date varchar(8),@filename varchar(30),@sql varchar(200) set @filename='c:\PL'+convert(char(8),getdate(),112)+'.txt' set @sql='bcp "Select * from #inserted" queryout'+' '+@filename+' '+'-c -t \t -SCTCNL066 -Uscca -Pscca' EXEC master..xp_cmdshell @sql DROP TABLE #inserted -- Disable xp_cmdshell EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE GO

执行以后报错
Msg 287, Level 16, State 2, Procedure UTR_ExportPL, Line 4
The CONFIGURE statement is not allowed within a trigger.
Msg 287, Level 16, State 2, Procedure UTR_ExportPL, Line 6
The CONFIGURE statement is not allowed within a trigger.
Msg 287, Level 16, State 2, Procedure UTR_ExportPL, Line 16
The CONFIGURE statement is not allowed within a trigger.
Msg 287, Level 16, State 2, Procedure UTR_ExportPL, Line 18
The CONFIGURE statement is not allowed within a trigger.

这个问题第1个回答：
我的意思是当表or033100中某个字段发生变化时，把inserted表里面，也就是发生变化的行提取出来，导出为txt格式！

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
这两条命令是启用bcp的，由于打开这两个选项有安全性问题，所以在执行完毕之后，需要再次关闭
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE

这个问题第2个回答：
SELECT * INTO #inserted FROM inserted
declare @date varchar(8),@filename varchar(30),@sql varchar(200)
set @filename='c:\PL'+convert(char(8),getdate(),112)+'.txt'
set @sql='bcp "Select * from #inserted" queryout'+' '+@filename+' '+'-c -t \t -SCTCNL066 -Uscca -Pscca'

这里的局部临时表有生命周期和作用域的问题，bcp时访问不到的。
用实表，或者全局临时表来处理。但是会有并发问题。

这个问题第3个回答：
那么我这个需求怎么实现比较好啊？？

这个问题第4个回答：
纯sql语句好像没什么完美的解决办法。

这个问题第5个回答：
既然人家说了"The CONFIGURE statement is not allowed within a trigger. "
那试试在trigger中调用存储过程,应该可以实现你的需求.

这个问题第6个回答：
可以将inserted表中的行以表变量的形式传给存储过程,这样就不会牵涉到作用域或并发问题了.

这个问题第7个回答：
在sp里面定义表变量的时候出错了
CREATE PROCEDURE USP_ExportPL
@inserted TABLE
(
OrderNo VARCHAR(12),PlNo VARCHAR(12),CustomerCode VARCHAR(8),DelAddr varchar(36),DelTerm VARCHAR(6),
ContactName VARCHAR(25),ContactNo INT(16),ConfirmedDelDate DATETIME,WH VARCHAR(12),StockCode VARCHAR(16),
Linenumber INT(3),Description VARCHAR(25),OrderQty DECIMAL(10)
)
AS

Msg 156, Level 15, State 1, Procedure USP_ExportPL, Line 2
Incorrect syntax near the keyword 'TABLE'.

这个问题第8个回答：
存储过程不支持表变量为输入参数。

这个问题第9个回答：
各位大大，有什么好的方法吗？

这个问题第10个回答：
我建立一个trigger和一个sp

SQL code




IF OBJECT_ID ('UTR_ExportPL', 'TR') IS NOT NULL

   DROP TRIGGER UTR_ExportPL

Go

CREATE TRIGGER UTR_ExportPL ON sdbc2..OR03t100

AFTER UPDATE AS

IF UPDATE(or03013)

IF (SELECT 1 FROM tempdb..sysobjects WHERE NAME='##insert' ) IS NOT NULL

   DROP TABLE ##INSERT



SELECT OR01001,OR03051,OR01004,SL01002,C.OR04003,OR01013,OR01018,OR01072,OR03001,OR03019,OR03046,OR03005,OR03002,OR03006,

OR03011 into ##insert FROM inserted

LEFT JOIN OR01t100 ON OR03001=OR01001

LEFT JOIN SL01t100 ON OR01004=SL01001

LEFT JOIN (

SELECT OR04001,OR04003 FROM OR04t100 RIGHT OUTER JOIN or01t100 ON OR04001=OR01001 WHERE OR04003 IS NOT NULL

AND or04001=(SELECT TOP (1) or03001 FROM inserted)

UNION ALL

SELECT A.OR01001,B.SL14004 FROM (

SELECT OR01001,OR01004 FROM OR04t100 RIGHT OUTER JOIN or01t100 ON OR04001=OR01001 WHERE OR04003 IS NULL

AND or01001=(SELECT TOP (1) or03001 FROM inserted)

) A

LEFT JOIN (SELECT * FROM SL14t100 WHERE SL14002='00') B ON A.OR01004=B.SL14001 

) as C

ON OR01001=C.OR04001



EXEC USP_ExportPL

DROP TABLE ##insert

SQL code




IF OBJECT_ID ('USP_ExportPL', 'P') IS NOT NULL

   DROP procedure USP_ExportPL

Go

CREATE PROCEDURE USP_ExportPL

AS

declare @date varchar(8),@filename varchar(30),@sql varchar(2000)

EXEC sp_configure 'show advanced options',1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE





set @filename='c:\PL'+convert(char(8),getdate(),112)+'.txt'

OR01001,OR03051,OR01004,SL01002,C.OR04003,OR01013,OR01018,OR01072,OR03001,OR03019,OR03046,OR03005,OR03002,OR03006,OR03011 FROM ##insert LEFT JOIN OR013100 ON OR03001=OR01001 LEFT JOIN SL013100 ON OR01004=SL01001 LEFT JOIN(SELECT OR04001,OR04003 FROM OR043100 RIGHT OUTER JOIN or013100 ON OR04001=OR01001 WHERE OR04003 IS NOT NULL AND or04001=(SELECT TOP (1) or03001 FROM ##insert) UNION ALL SELE
[1] [2] 下一页