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
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