1 USE [ NF_UserData ] 2 GO 3 SET ANSI_NULLS ON 4 GO 5 SET QUOTED_IDENTIFIER ON 6 GO 7 -- ============================================= 8 -- Author: IsaacZhang 9 -- Create date: 2012-09-04 10 -- Description: 创建插入hubble索引中间表数据的触发器 11 -- ============================================= 12 CREATE TRIGGER Trigger_UserDataBibliography_Insert 13 ON Bibliography 14 AFTER INSERT 15 AS 16 BEGIN 17 /* 声明需要到的变量@hubbleID int, */ 18 DECLARE 19 @BibliographyId uniqueidentifier, 20 @BibliographyIntId int, -- 暂时没有这个字段 21 @Title varchar( 255), 22 @AuthorsXml XML, -- 需要处理 23 @Authors varchar( 500), -- 等待XML文件处理完成后写入字段 24 @SharePersonName varchar ( 100), -- 暂时无这个 25 @ShareDate datetime , 26 @Media varchar ( 255), 27 @IsFullFile bit , -- 需要在外键中处理 28 @FileId uniqueidentifier, -- 获取全文ID 29 @SharePersonId int , -- 暂时无这个 30 @BibAbstract nvarchar ( max), 31 @DOI nvarchar ( 128), 32 @PubulishYear int , 33 @Volume nvarchar ( 32), 34 @Issue nvarchar ( 32), 35 @PageScope nvarchar ( 50), 36 @PageCount int , 37 @TagStr nvarchar ( 1000), -- 外键表中找 38 @TagIDStr nvarchar ( 1000), -- 外键表中找，与标签字串一一对应 39 @Keywords nvarchar( 1000), -- 40 @KeywordsXML XML, 41 @UserID uniqueidentifier 42 43 -- 从插入记录中取出一部分数据 44 SELECT @BibliographyId = BibliographyId, @Title =Title, @AuthorsXml = CAST(Authors AS XML) 45 , @UserID =UserID, @ShareDate = ShareDate, @Media =Media, @BibAbstract = Abstract 46 , @DOI =DOI, @PubulishYear = [ Year ], @Volume = Volume, @Issue = Issue 47 , @PageScope = PageScope, @PageCount = [ PageCount ], @KeywordsXML = CAST(Keywords AS XML) 48 FROM INSERTED 49 50 -- 判断是否有全文 51 SET @IsFullFile = 0 52 DECLARE @i INT 53 SELECT @i = COUNT( 0) FROM NPU_File.dbo. [ File ] WHERE FileId = @FileId 54 IF @i > 0 55 SET @IsFullFile = 1 56 -- 转换作者信息 57 SET @Authors = ''; 58 DECLARE @isHas INT; 59 select @isHas = @AuthorsXml.exist( ' declare default element namespace "http://services.notefirst.com/Type/Author";//FullName '); 60 IF @isHas > 0 61 BEGIN 62 -- 声明一个游标 63 DECLARE authorCursor CURSOR 64 FOR 65 select T.C.value( ' declare default element namespace "http://services.notefirst.com/Type/Author";(../FullName)[1] ', ' varchar(200) ') 66 as authorstr 67 from @AuthorsXml.nodes( ' declare default element namespace "http://services.notefirst.com/Type/Author";(//FullName) ') as T(C) 68 OPEN authorCursor 69 -- 循环一个游标 70 DECLARE @tempAuthor NVARCHAR( 50); 71 FETCH NEXT FROM authorCursor INTO @tempAuthor 72 WHILE @@FETCH_STATUS = 0 73 BEGIN 74 -- 拼接作者信息，用逗号隔开 75 SET @Authors += @tempAuthor + ' , '; 76 FETCH NEXT FROM authorCursor INTO @tempAuthor 77 END 78 SET @Authors = substring( @Authors, 1, len( rtrim( @Authors)) - 1) 79 -- 关闭游标 80 CLOSE authorCursor 81 -- 释放资源 82 DEALLOCATE authorCursor 83 84 END -- 结束作者信息IF 85 86 -- 查看标签信息 87 SET @TagIDStr = ''; 88 SET @TagStr = ''; 89 IF( SELECT COUNT( 0) FROM dbo. [ Relationship-Bibliography^SysTag ] WHERE BibliographyId = @BibliographyId) > 0 90 BEGIN 91 DECLARE tagCursor CURSOR 92 FOR SELECT TagId FROM dbo. [ Relationship-Bibliography^SysTag ] WHERE BibliographyId = @BibliographyId 93 OPEN tagCursor 94 -- 循环一个游标 95 DECLARE @tempTag NVARCHAR( 50), @tempTagID NVARCHAR( 50); 96 FETCH NEXT FROM tagCursor INTO @tempTagID 97 WHILE @@FETCH_STATUS = 0 98 BEGIN 99 SET @TagIDStr += @tempTagID + ' , '; 100 SET @TagStr += @tempTag + ' , '; 101 END 102 SET @TagIDStr = substring( @TagIDStr, 1, len( rtrim( @TagIDStr)) - 1) 103 SET @TagStr = substring( @TagStr, 1, len( rtrim( @TagStr)) - 1) 104 -- 关闭游标 105 CLOSE tagCursor 106 -- 释放资源 107 DEALLOCATE tagCursor 108 END 109 110 -- 查询关键词信息 111 SET @Keywords = ''; 112 DECLARE @KeywordsTemp nvarchar( 50); 113 DECLARE @isHasKeywords INT; 114 select @isHasKeywords = @KeywordsXml.exist( ' //string '); 115 IF @isHasKeywords > 0 116 BEGIN 117 -- 统计有多少关键词 118 DECLARE @countKeyword Int; 119 SET @countKeyword = 0; 120 select @countKeyword = T.S.value( ' count(//string) ', ' Int ') 121 from @KeywordsXml.nodes( ' //ArrayOfString ') as T(S) 122 WHILE @countKeyword > 0 123 BEGIN 124 125 select @KeywordsTemp = T.S.value( ' (//string[sql:variable( "@countKeyword")])[1] ', ' Int ') 126 from @KeywordsXml.nodes( ' //ArrayOfString ') as T(S) 127 128 SET @Keywords += @KeywordsTemp + ' , ' 129 SET @countKeyword = @countKeyword - 1; 130 END 131 SET @Keywords = substring( @Keywords, 1, len( rtrim( @Keywords)) - 1) 132 END -- 结束关键词信息IF 133 134 INSERT INTO dbo.Hubble_UserDataBibliography 135 ( [ BibliographyId ] 136 , [ BibliographyIntId ] 137 , [ Title ] 138 , [ Authors ] 139 , [ SharePersonName ] 140 , [ ShareDate ] 141 , [ Media ] 142 , [ IsFullFile ] 143 , [ FileId ] 144 , [ SharePersonId ] 145 , [ BibAbstract ] 146 , [ DOI ] 147 , [ PubulishYear ] 148 , [ Volume ] 149 , [ Issue ] 150 , [ PageScope ] 151 , [ PageCount ] 152 , [ TagStr ] 153 , [ TagIDStr ] 154 , [ Keywords ] 155 , [ UserID ]) 156 VALUES( @BibliographyId, 157 0, 158 @Title, 159 @Authors, 160 '', 161 @ShareDate, 162 @Media, 163 @IsFullFile, 164 @FileId, 165 0, 166 @BibAbstract, 167 @DOI, 168 @PubulishYear, 169 @Volume, 170 @Issue, 171 @PageScope, 172 @PageCount, 173 @TagStr, 174 @TagIDStr, 175 @Keywords, 176 @UserID); 177 END 178 179 GO