文本导入数据库后 用insert into 方式 无法触发触发器 怎么解决
create trigger tr_userLog_insert
on A1 FOR INSERT
as
begin
declare
@v_userid int,--用户Id
@v_stamp int,--邮戳
@v_identify nvarchar(4),--标识(page,base,play,cost)
@v_pageurl nvarchar(120),--页面url
@v_usertoken nvarchar(32),--用户标识
@v_accesstime datetime,--访问时间
@v_sourcefilename nvarchar(32),--视频原文件
@v_stbid nvarchar(24),--机顶盒id
@v_fee nvarchar(32),--计费代码
@v_orderdesc nvarchar(50);
select @v_accesstime = convert(datetime,subString(dic,7,19),120),--accesstime
@v_userid = subString(dic,40,9),--userid
@v_identify = subString(dic,82,4),--identify
@v_pageurl = subString(dic,88,120),--pageurl
@v_usertoken = subString(dic,167,32),--usertoken
@v_sourcefilename = subString(dic,88,9),--sourcefilename
@v_stamp = SubString(dic,73,7),--stamp
@v_stbid = subString(dic,132,24),--stbid
@v_fee = subString(dic,88,10), --fee
@v_orderdesc = SubString(dic,109,50) from inserted
select @v_userid 用户ID,@v_identify 表标识,@v_stamp 区域代码,@v_pageurl 页面地址,@v_accesstime 访问时间;
if exists(select *from inserted where dic like '%auth_info%') --用户基本表
begin
insert into UserBase(userid,stamp,identify,stbid,usertoken,pageurl,accesstime,other)values(@v_userid,@v_stamp,@v_identify,@v_stbid,@v_usertoken,@v_pageurl,@v_accesstime,'');
end
if exists(select *from inserted where subString(dic,82,4)='play' )--播放表
begin
insert into UserPlaySongData(userid,stamp,identify,sourcefilename,pageurl,accesstime,other)values(@v_userid,@v_stamp,@v_identify,@v_sourcefilename,@v_pageurl,@v_accesstime,'');
end
if exists(select *from inserted ) --用户日志表
begin
insert into UserLog(userid,pageurl,identify,stamp,accesstime,other)values(@v_userid,@v_pageurl,@v_identify,@v_stamp,@v_accesstime,'');
end
if exists(select *from inserted where dic like '%cost%')--计费表
begin
insert into UserCharge(userid,identify,stamp,fee,orderdesc,pageurl,accesstime,other)values(@v_userid,@v_identify,@v_stamp,@v_fee,@v_orderdesc,@v_pageurl,@v_accesstime,'');
end
end
-------------------------------------------
然后 文本数据导入到表A中 将表复制到A1中
Insert into A1 select dic from A
为什么触发器只执行了一次 我想让他插入多少条数据 就执行几次触发器。 这样的话 该怎么做 跪求高手啊，。。
[最优解释]
为什么只处理依次，关键的地方在这儿
select @v_accesstime = convert(datetime,subString(dic,7,19),120),--accesstime
@v_userid = subString(dic,40,9),--userid
@v_identify = subString(dic,82,4),--identify
@v_pageurl = subString(dic,88,120),--pageurl
@v_usertoken = subString(dic,167,32),--usertoken
@v_sourcefilename = subString(dic,88,9),--sourcefilename
@v_stamp = SubString(dic,73,7),--stamp
@v_stbid = subString(dic,132,24),--stbid
@v_fee = subString(dic,88,10), --fee
@v_orderdesc = SubString(dic,109,50) from inserted
inserted是有很多数据，但是这个select取的是inserted的最后一条数据，自然只处理一次。你如果要处理多次，那你可以使用游标，取出一条数据，然后再进行处理
[其他解释]
[其他解释]
试试游标吧，不然你这样肯定之触发一次
[其他解释]
我想问一下 如果要用游标的话 怎么写呢， 希望你能帮我一下
[其他解释]
只提供思路和方法，具体实现，自己去做。自己动手去做，才会有提高，吃现成的永远提高不了
[其他解释]
我写了 一下 没有语法错误 但是执行的时候 不对 你帮我看看是哪里的问题..
create trigger tr_userLog_insert1
on A1 FOR INSERT
as
declare
@v_userid int,--用户Id
@v_stamp int,--邮戳
@v_identify nvarchar(4),--标识(page,base,play,cost)
@v_pageurl nvarchar(120),--页面url
@v_usertoken nvarchar(32),--用户标识
@v_accesstime datetime,--访问时间
@v_sourcefilename nvarchar(32),--视频原文件
@v_stbid nvarchar(24),--机顶盒id
@v_fee nvarchar(32),--计费代码
@v_orderdesc nvarchar(50);
select @v_accesstime = convert(datetime,subString(dic,7,19),120),--accesstime
@v_userid = subString(dic,40,9),--userid
@v_identify = subString(dic,82,4),--identify
@v_pageurl = subString(dic,88,120),--pageurl
@v_usertoken = subString(dic,167,32),--usertoken
@v_sourcefilename = subString(dic,88,9),--sourcefilename
@v_stamp = SubString(dic,73,7),--stamp
@v_stbid = subString(dic,132,24),--stbid
@v_fee = subString(dic,88,10), --fee
@v_orderdesc = SubString(dic,109,50) from inserted
select @v_userid 用户ID,@v_identify 表标识,@v_stamp 区域代码,@v_pageurl 页面地址,@v_accesstime 访问时间;
open cursor1--打开游标
fetch next from cursor1 into @v_accesstime,@v_userid,@v_identify,@v_pageurl,@v_usertoken,@v_sourcefilename,@v_stamp,@v_stbid,@v_fee,@v_orderdesc;
while @@fetch_status=0--判断是否成功获取数据
begin
if exists(select *from inserted where dic like '%auth_info%') --用户基本表
begin
insert into UserBase(userid,stamp,identify,stbid,usertoken,pageurl,accesstime,other)values(@v_userid,@v_stamp,@v_identify,@v_stbid,@v_usertoken,@v_pageurl,@v_accesstime,'');
end
if exists(select *from inserted where subString(dic,82,4)='play' )--播放表
begin
insert into UserPlaySongData(userid,stamp,identify,sourcefilename,pageurl,accesstime,other)values(@v_userid,@v_stamp,@v_identify,@v_sourcefilename,@v_pageurl,@v_accesstime,'');
end
if exists(select *from inserted ) --用户日志表
begin
insert into UserLog(userid,pageurl,identify,stamp,accesstime,other)values(@v_userid,@v_pageurl,@v_identify,@v_stamp,@v_accesstime,'');
end
if exists(select *from inserted where dic like '%cost%')--计费表
begin
insert into UserCharge(userid,identify,stamp,fee,orderdesc,pageurl,accesstime,other)values(@v_userid,@v_identify,@v_stamp,@v_fee,@v_orderdesc,@v_pageurl,@v_accesstime,'');
end
fetch next from cursor1 into @v_accesstime,@v_userid,@v_identify,@v_pageurl,@v_usertoken,@v_sourcefilename,@v_stamp,@v_stbid,@v_fee,@v_orderdesc;
end
close cursor1;
[其他解释]
我现在已经改成游标的样子了 但是 还是有错。 谁可以帮我看一下 是哪里错误啊
DECLARE
@value nvarchar(500),
@v_userid int,--用户Id
@v_stamp int,--邮戳
@v_identify nvarchar(4),--标识(page,base,play,cost)
@v_pageurl nvarchar(120),--页面url
@v_usertoken nvarchar(32),--用户标识
@v_accesstime datetime,--访问时间
@v_sourcefilename nvarchar(32),--视频原文件
@v_stbid nvarchar(24),--机顶盒id
@v_fee nvarchar(32),--计费代码
@v_orderdesc nvarchar(50)
BEGIN
-- 定义游标.
declare cursor1 cursor for
SELECT dic from A1;
-- 打开游标.
OPEN cursor1;
fetch next from cursor1 into @value
while @@fetch_status=0
select @v_accesstime = convert(datetime,subString(@value,7,19),120),--accesstime
@v_userid = subString(@value,40,9),--userid
@v_identify = subString(@value,82,4),--identify
@v_pageurl = subString(@value,88,120),--pageurl
@v_usertoken = subString(@value,167,32),--usertoken
@v_sourcefilename = subString(@value,88,9),--sourcefilename
@v_stamp = SubString(@value,73,7),--stamp
@v_stbid = subString(@value,132,24),--stbid
@v_fee = subString(@value,88,10), --fee
@v_orderdesc = SubString(@value,109,50) from A1
BEGIN
if exists(select *from A1 where @value like '%auth_info%') --用户基本表
begin
insert into UserBase(userid,stamp,identify,stbid,usertoken,pageurl,accesstime,other)values(@v_userid,@v_stamp,@v_identify,@v_stbid,@v_usertoken,@v_pageurl,@v_accesstime,'');
end
if exists(select *from A1 where subString(@value,82,4)='play' )--播放表
begin
insert into UserPlaySongData(userid,stamp,identify,sourcefilename,pageurl,accesstime,other)values(@v_userid,@v_stamp,@v_identify,@v_sourcefilename,@v_pageurl,@v_accesstime,'');
end
if exists(select *from A1 ) --用户日志表
begin
insert into UserLog(userid,pageurl,identify,stamp,accesstime,other)values(@v_userid,@v_pageurl,@v_identify,@v_stamp,@v_accesstime,'');
end
if exists(select *from A1 where @value like '%cost%')--计费表
begin
insert into UserCharge(userid,identify,stamp,fee,orderdesc,pageurl,accesstime,other)values(@v_userid,@v_identify,@v_stamp,@v_fee,@v_orderdesc,@v_pageurl,@v_accesstime,'');
end
fetch next from c into @value
END
CLOSE cursor1
deallocate cursor1
[其他解释]
晕，为啥要用游标啊。。。修改下触发器就可以了
举个例子
if exists(select *from inserted where dic like '%auth_info%') --用户基本表
begin
insert into UserBase(userid,stamp,identify,stbid,usertoken,pageurl,accesstime,other)values(@v_userid,@v_stamp,@v_identify,@v_stbid,@v_usertoken,@v_pageurl,@v_accesstime,'');
end
改成：
if exists(select *from inserted where dic like '%auth_info%') --用户基本表
begin
insert into UserBase(userid,stamp,identify,stbid,usertoken,pageurl,accesstime,other)
select v_userid,v_stamp,v_identify,v_stbid,v_usertoken,v_pageurl,v_accesstime,''
from inserted
end
其他部分一样的。