请问:为什么触发器触发后不能退出,还死机,触发器有什么限制的?
我写的触发器材如下:单单执行触发器中的SQL语句是没问题的
ALTER TRIGGER [TR_BlogArticleCreateXMLFile]
ON [dbo].[T_SNETBlogArticle]
AFTER INSERT
AS
BEGIN
DECLARE @Str NVARCHAR(2000)
DECLARE @Par NVARCHAR(100)
DECLARE @Span NVARCHAR(50)
--DECLARE @Const NVARCHAR(500)
CREATE TABLE tb(PartName NVARCHAR(100))
INSERT tb SELECT ' <channel>'
UNION ALL SELECT ' <title>Liftoff News</title>'
UNION ALL SELECT ' <link>http://liftoff.msfc.nasa.gov</link>'
UNION ALL SELECT ' <description>Liftoff to Space Exploration.</description>'
UNION ALL SELECT ' <title>Liftoff News</title>'
UNION ALL SELECT ' <pubDate>Tue, 10 Jun 2003 04:00:00 GMT</pubDate>'
UNION ALL SELECT ' <lastBuildDate>Tue, 10 Jun 2003 09:41:01 GMT</lastBuildDate>'
UNION ALL SELECT ' <docs>http://blogs.law.harvard.edu/tech/rss</docs>'
UNION ALL SELECT ' <generator>Weblog Editor 2.0</generator>'
UNION ALL SELECT ' <managingEditor>[email protected]</managingEditor>'
UNION ALL SELECT ' <webMaster>[email protected]</webMaster>'
--==============================================================================================
--select top 10 * INTO #tb from T_SNETBlogArticle ORDER BY SNETBlogArticleCreateDate DESC
--==============================================================================================
SET @Span = '+char(13)+char(10)+'
SET @Par = '" queryout "G:\NWeb\a.xml" /S"B992586A0529472\SNET2008T" /U"sa" /P"123456" /c'
set @Str= 'bcp "select' + '''<?xml version="1.0"?>'''
+'union all select' + '''<rss version="2.0">'''
+ 'union all (select PartName FROM test.dbo.tb)'
+'union all (select top 10' + ''' <item>''' + @Span
+''' <title>''' + '+SNETBlogArticleTitle+' + '''</title>'''+ @Span
+''' <link>''' + '+SNETBlogArticleTitle+' + '''</link>''' + @Span
+''' <description>''' + '+SNETBlogArticleTitle+' + '''</description>''' + @Span
+''' <pubDate>''' + '+CONVERT(NVARCHAR(15),SNETBlogArticleCreateDate,23)+' + '''<pubDate>''' + @Span
+''' <guid>''' + '+SNETBlogArticleTitle+' + '''</guid>''' + @Span
+''' </item>'''
+'from (select top 10 * from test.dbo.T_SNETBlogArticle ORDER BY SNETBlogArticleCreateDate DESC) AS T_Article ) union all select'
+''' </channel>'''
+'union all select' + '''</rss>'''
+ @Par
exec master..xp_cmdshell @Str
DROP TABLE tb
--DROP TABLE #tbEND
我写的触发器材如下:单单执行触发器中的SQL语句是没问题的
ALTER TRIGGER [TR_BlogArticleCreateXMLFile]
ON [dbo].[T_SNETBlogArticle]
AFTER INSERT
AS
BEGIN
DECLARE @Str NVARCHAR(2000)
DECLARE @Par NVARCHAR(100)
DECLARE @Span NVARCHAR(50)
--DECLARE @Const NVARCHAR(500)
CREATE TABLE tb(PartName NVARCHAR(100))
INSERT tb SELECT ' <channel>'
UNION ALL SELECT ' <title>Liftoff News</title>'
UNION ALL SELECT ' <link>http://liftoff.msfc.nasa.gov</link>'
UNION ALL SELECT ' <description>Liftoff to Space Exploration.</description>'
UNION ALL SELECT ' <title>Liftoff News</title>'
UNION ALL SELECT ' <pubDate>Tue, 10 Jun 2003 04:00:00 GMT</pubDate>'
UNION ALL SELECT ' <lastBuildDate>Tue, 10 Jun 2003 09:41:01 GMT</lastBuildDate>'
UNION ALL SELECT ' <docs>http://blogs.law.harvard.edu/tech/rss</docs>'
UNION ALL SELECT ' <generator>Weblog Editor 2.0</generator>'
UNION ALL SELECT ' <managingEditor>[email protected]</managingEditor>'
UNION ALL SELECT ' <webMaster>[email protected]</webMaster>'
--==============================================================================================
--select top 10 * INTO #tb from T_SNETBlogArticle ORDER BY SNETBlogArticleCreateDate DESC
--==============================================================================================
SET @Span = '+char(13)+char(10)+'
SET @Par = '" queryout "G:\NWeb\a.xml" /S"B992586A0529472\SNET2008T" /U"sa" /P"123456" /c'
set @Str= 'bcp "select' + '''<?xml version="1.0"?>'''
+'union all select' + '''<rss version="2.0">'''
+ 'union all (select PartName FROM test.dbo.tb)'
+'union all (select top 10' + ''' <item>''' + @Span
+''' <title>''' + '+SNETBlogArticleTitle+' + '''</title>'''+ @Span
+''' <link>''' + '+SNETBlogArticleTitle+' + '''</link>''' + @Span
+''' <description>''' + '+SNETBlogArticleTitle+' + '''</description>''' + @Span
+''' <pubDate>''' + '+CONVERT(NVARCHAR(15),SNETBlogArticleCreateDate,23)+' + '''<pubDate>''' + @Span
+''' <guid>''' + '+SNETBlogArticleTitle+' + '''</guid>''' + @Span
+''' </item>'''
+'from (select top 10 * from test.dbo.T_SNETBlogArticle ORDER BY SNETBlogArticleCreateDate DESC) AS T_Article ) union all select'
+''' </channel>'''
+'union all select' + '''</rss>'''
+ @Par
exec master..xp_cmdshell @Str
DROP TABLE tb
--DROP TABLE #tbEND
CREATE TABLE tb(PartName NVARCHAR(100)) 在事务没有提交的情况下, 调用 xp_cmdshell 执行 bcp , 这里面的进程与触发器不是同一个, 既然不是同一个, bcp的操作肯定被触发器阻塞, 所以必须等, 而触发器不结束, 事务不提交, 所以这个等永远没有尽头
所以不死才怪
这样可以不加锁的读取到数据
1是:CREATE TABLE tb(PartName NVARCHAR(100))
2是:select top 10 * from test.dbo.T_SNETBlogArticle
我说的对吗?
受益良多