SQL触发器！

Question

数据：
     CurrentPage       
http://item.taobao.com/item.htm?id=8551767969
http://item.taobao.com/item.htm?id=7887360289
http://item.taobao.com/item.htm?id=8753156661?ProdID=8402215193
http://item.taobao.com/item.htm?id=8033668987
http://item.taobao.com/item.htm?id=8232210597&cm_cat=50010531&pm2=4http://item.taobao.com/item.htm?id=7734268589
http://item.taobao.com/item.htm?id=7781868337&cm_cat=50023854&pm2=1
http://item.taobao.com/item.htm?id=8551767969?ProdID=72813829&stop=48592839
http://item.taobao.com/item.htm?id=8551767969
http://item.taobao.com/item.htm?id=7183998685有如上数据，建立一个触发器更新的！
判断CurrentPage 是不是  like '%ProdID=%',如果是的，则提取CurrentPage,和ProdID=8402215193的值，插入表showdata中！
如上数据表showdata应得到的数据为:
CurrentPage                                                                          ProdID
http://item.taobao.com/item.htm?id=8753156661?ProdID=8402215193                     8402215193
http://item.taobao.com/item.htm?id=8551767969?ProdID=72813829&stop=4859283922       4859283922  
请问这个触发器应该怎么写？           
 

Answer 1

第二条数据好像提取不对呀 是progid吗

Answer 2

发错发错，不好意思：
CurrentPage                                                                   ProdID
http://item.taobao.com/item.htm?id=8753156661?ProdID=8402215193                     8402215193
http://item.taobao.com/item.htm?id=8551767969?ProdID=72813829&stop=4859283922       72813829

Answer 3

create table tb(CurrentPage nvarchar(200))
create table showdata(currentpage nvarchar(200),prodid nvarchar(20))
go
create trigger getprod
on tb
after insert
as
begin
insert into showdata
select currentpage,substring(currentpage,charindex('ProdID=',currentpage)+7,
(case when charindex('&',currentpage,charindex('ProdID=',currentpage)+7)>0 then charindex('&',currentpage,charindex('ProdID=',currentpage)+7)-1 else len(currentpage) end)-charindex('ProdID=',currentpage)-6)
from tb where charindex('ProdID=',currentpage)>0
end
go
insert into tb 
select 'http://item.taobao.com/item.htm?id=8551767969' union all
select 'http://item.taobao.com/item.htm?id=7887360289' union all
select 'http://item.taobao.com/item.htm?id=8753156661?ProdID=8402215193' union all
select 'http://item.taobao.com/item.htm?id=8033668987' union all
select 'http://item.taobao.com/item.htm?id=8232210597&cm_cat=50010531&pm2=4' union all
select 'http://item.taobao.com/item.htm?id=7734268589' union all
select 'http://item.taobao.com/item.htm?id=7781868337&cm_cat=50023854&pm2=1' union all
select 'http://item.taobao.com/item.htm?id=8551767969?ProdID=72813829&stop=48592839' union all
select 'http://item.taobao.com/item.htm?id=8551767969' union all
select 'http://item.taobao.com/item.htm?id=7183998685'
select * from showdata
go
drop table tb,showdata
/*
currentpage                                                                                                                                                                                              prodid
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
http://item.taobao.com/item.htm?id=8753156661?ProdID=8402215193                                                                                                                                          8402215193
http://item.taobao.com/item.htm?id=8551767969?ProdID=72813829&stop=48592839                                                                                                                              72813829(2 行受影响)
*/

Answer 4

ProdID=?这个ID没提取出来啊

Answer 5

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb (CurrentPage varchar(112))
insert into #tb
select 'http://item.taobao.com/item.htm?id=8551767969' union all
select 'http://item.taobao.com/item.htm?id=7887360289' union all
select 'http://item.taobao.com/item.htm?id=8753156661?ProdID=8402215193' union all
select 'http://item.taobao.com/item.htm?id=8033668987' union all
select 'http://item.taobao.com/item.htm?id=8232210597&cm_cat=50010531&pm2=4http://item.taobao.com/item.htm?id=7734268589' union all
select 'http://item.taobao.com/item.htm?id=7781868337&cm_cat=50023854&pm2=1' union all
select 'http://item.taobao.com/item.htm?id=8551767969?ProdID=72813829&stop=48592839' union all
select 'http://item.taobao.com/item.htm?id=8551767969' union all
select 'http://item.taobao.com/item.htm?id=7183998685'select CurrentPage,
  ProdID=substring(right(CurrentPage,len(CurrentPage)-charindex('ProdID',CurrentPage)-6),1,
 patindex('%[^0-9]%',right(CurrentPage,len(CurrentPage)-charindex('ProdID',CurrentPage)-6)+'#')-1)
from #tb
where charindex('ProdID',CurrentPage)>0
CurrentPage                                                                                                      ProdID
---------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------
http://item.taobao.com/item.htm?id=8753156661?ProdID=8402215193                                                  8402215193
http://item.taobao.com/item.htm?id=8551767969?ProdID=72813829&stop=48592839                                      72813829(2 行受影响)