Limpire 还有一个小问题

[flush_520]

你的程序在此

CREATE trigger up_company_sort on Asean_company 
for insert,update,delete
as
set nocount on
delete b from Asean_company_sort  as b where exists (select 1 from DELETED where c_userid=b.c_userid)
select id=identity(int,1,1), b.num,a.c_userid,bigclass=substring(a.c_productsbclas+',',b.num+1,charindex(',',a.c_productsbclas+',',b.num+1)-b.num-1), flag=right(a.c_productsbclas,1)+right(a.c_productssclas,1) into #1 from INSERTED as a, nums as b where substring(a.c_productsbclas,b.num,1)=','
select id=identity(int,1,1), b.num,a.c_userid,smallclass=substring(a.c_productssclas+',',b.num+1,charindex(',',a.c_productssclas+',',b.num+1)-b.num-1) into #2 from INSERTED as a, nums as b where substring(a.c_productssclas,b.num,1)=','
delete #1 where bigclass=''
delete #2 where smallclass=''
insert #1 select 0, c_userid, bigclass, null from #1 as t where flag=',,' and not exists (select 1 from #1 where c_userid=t.c_userid and  ltrim(bigclass)=ltrim(t.bigclass) and id<t.id)
update #1 set num=(select count(1) from #1 where c_userid=t.c_userid and id<=t.id) from #1 as t
update #2 set num=(select count(1) from #2 where c_userid=t.c_userid and id<=t.id) from #2 as t
insert  Asean_company_sort(c_userid,company,bclass,sclass) select a.c_userid,a.c_name,b.bigclass,c.smallclass from INSERTED as a join #1 as b on a.c_userid=b.c_userid left join #2 as c on a.c_userid=c.c_userid and b.num=c.num order by b.c_userid, b.num
set nocount off

现在什么都可以了 但还有个小问题就是：更新
当我更新Asean_company的时候 Asean_company_sort里面的相关信息是先删除后插入，Asean_company_sort里面有个手工排序字段也就废了，有什么方法可以改进下吗？就是没有改变的大类或小类就不动它（不让它先删除后插入）

[flush_520]

--直接去掉：
from 
    Asean_company_sort a
where
--才无错误
--运行：update  Asean_company  set c_hits='64' where id='4'

服务器: 消息 207，级别 16，状态 3，过程 up_company_sort，行 21
列名 'bigclass' 无效。
服务器: 消息 207，级别 16，状态 1，过程 up_company_sort，行 22
列名 'id' 无效。
服务器: 消息 207，级别 16，状态 1，过程 up_company_sort，行 22
列名 'id' 无效。
服务器: 消息 207，级别 16，状态 1，过程 up_company_sort，行 23
列名 'id' 无效。
服务器: 消息 207，级别 16，状态 1，过程 up_company_sort，行 23
列名 'id' 无效。

[flush_520]

不仅不能更新asean-company-sort表 就连asean-company也不能更新。我只测试了更新 继续测试.....

[flush_520]

删除可以，更新和插入错误一样，
Asean_company表中的大类和小类前后都有逗号(,157,158,160,161,)

[中国风]

把兩個表結構和數據庫貼出來

[中国风]

CREATE trigger up_company_sort on Asean_company 
for insert,update,delete
as
set nocount on
if not exists(select 1 from inserted)--判斷刪除
    begin
        delete b from     Asean_company_sort  as b where exists     (select 1 from DELETED where c_userid=b.c_userid)
        return
    end

select b.num,a.c_userid,bigclass=substring(a.c_productsbclas,b.num,charindex(',',c_productsbclas+',',b.num)-b.num)
into #1 
from INSERTED as a, nums as b 
where charindex(',',','+a.c_productsbclas,b.num)=b.num 

select     b.num,a.c_userid,smallclass=substring(a.c_productssclas,b.num,charindex(',',c_productssclas+',',b.num)-b.num) 
into #2 
from INSERTED as a, nums as b 
where charindex(',',','+a.c_productssclas,b.num)=b.num 

delete #1 where bigclass=''
delete #2 where bigclass=''

update #1 set num=(select count(1) from #1 where c_userid=t.c_userid and num<=t.num) from #1 as t 

update #2 set num=(select count(1) from #2 where c_userid=t.c_userid and num<=t.num) from #2 as t

select 
    b.c_userid,[company]=a.c_name,[bclass]=b.bigclass,[sclass]=c.smallclass 
into #3
from 
     #1 as b 
left join 
	#2 as c on a.c_userid=c.c_userid and b.num=c.num order by b.c_userid, b.num

from 
    Asean_company_sort a


delete b    --刪除大小類其中有改動的
from     
    #3    a
right join
    Asean_company_sort b on a.c_userid=b.c_userid and a.bclass=b.bclass and a.sclass=b.sclass
where
    a.c_userid is null

--增加
insert  Asean_company_sort
    (c_userid,company,bclass,sclass) 
select 
    a.*
from     
    #3    a
left join
    Asean_company_sort b on a.c_userid=b.c_userid and a.bclass=b.bclass and a.sclass=b.sclass
where
    b.c_userid is null

    

set nocount off

[中国风]

CREATE trigger up_company_sort on Asean_company 
for insert,update,delete
as
set nocount on
if not exists(select 1 from inserted)--判斷刪除
    begin
        delete b from     Asean_company_sort  as b where exists     (select 1 from DELETED where c_userid=b.c_userid)
        return
    end

select b.num,a.c_userid,bigclass=substring(a.c_productsbclas,b.num,charindex(',',c_productsbclas+',',b.num)-b.num)
into #1 
from INSERTED as a, nums as b 
where charindex(',',','+a.c_productsbclas,b.num)=b.num 

select     b.num,a.c_userid,smallclass=substring(a.c_productssclas,b.num,charindex(',',c_productssclas+',',b.num)-b.num) 
into #2 
from INSERTED as a, nums as b 
where charindex(',',','+a.c_productssclas,b.num)=b.num 

delete #1 where bigclass=''
delete #2 where bigclass=''

update #1 set num=(select count(1) from #1 where c_userid=t.c_userid and num<=t.num) from #1 as t 

update #2 set num=(select count(1) from #2 where c_userid=t.c_userid and num<=t.num) from #2 as t

select     b.c_userid,[company]=a.c_name,[bclass]=b.bigclass,[sclass]=c.smallclass 
into #3
from      #1 as b 
left join 	#2 as c on a.c_userid=c.c_userid and b.num=c.num order by b.c_userid, b.num

delete b    --刪除大小類其中有改動的
from        #3    a
right join    Asean_company_sort b on a.c_userid=b.c_userid and a.bclass=b.bclass and a.sclass=b.sclass
where    a.c_userid is null

--增加
insert  Asean_company_sort  (c_userid,company,bclass,sclass) 
select    a.*
from     #3    a left join     Asean_company_sort b on a.c_userid=b.c_userid and a.bclass=b.bclass and a.sclass=b.sclass
where     b.c_userid is null

    

set nocount off

[中国风]

上面改一下列名指定錯誤。。。

[flush_520]

Asean-company中的字段很多 在此用到的就只有
主键 公司名称 公司帐号 公司所属大类 小类
id c_name c_userid c_productsbclas c_productssclas

Asean-company-sort表
主键 公司名称 公司帐号 公司所属大类 小类 排序
id company c_userid bclass sclass sort

Asean-company-sort表与Asean-company联系外键为c-userid

Asean-company的更新、插入、删除都要与Asean-company-sort保持一致。

上面那个程序是插入、删除已经OK，
更新为了保持一致而采用先删除，后插入的方式，这样你不管修改大类或小类都保持了一致，只是更新的时候因为先删除后插入，所以我的sort排序字段也更着变为默认值了。

我想的是搞个事务来控制，当要删除Asean-company-sort表中内容的时候先判断Asean-company-sort表里面的大类和小类是否存在于更新表（inserted）里面，如果存在那就不删除

接下来再在Asean-company-sort表里面搞个触发器禁止重复插入。

想是这么想 但搞不出来 也不知道还有其他办法没

[flush_520]

select     b.c_userid,[company]=a.c_name,[bclass]=b.bigclass,[sclass]=c.smallclass 
into #3
from      #1 as b 
left join     #2 as c on a.c_userid=c.c_userid and b.num=c.num order by b.c_userid, b.num

出错！
select三个表而from两个表

[中国风]

好的，= =測測看

[中国风]

if object_id('nums') is not null drop table nums
select top 1000 num=identity(int,1,1) into nums from syscolumns,sysobjects
go
alter table nums add constraint pk_nums primary key (num)
go


create table Asean_company(id int identity,c_name nvarchar(100), c_userid int, c_productsbclas nvarchar(100), c_productssclas nvarchar(100))
create table Asean_company_sort(id int identity,company nvarchar(100),c_userid int,bclass nvarchar(10), sclass nvarchar(10),sort bigint)

go

if object_id('up_company_sort') is not null
	drop trigger up_company_sort
go
create trigger up_company_sort on Asean_company 
for insert,update,delete
as
set nocount on
if not exists(select 1 from inserted)--判斷刪除
	begin
		delete b from 	Asean_company_sort  as b where exists 	(select 1 from DELETED where c_userid=b.c_userid)
		return
	end

select b.num,a.c_name,a.c_userid,bigclass=substring(a.c_productsbclas,b.num,charindex(',',c_productsbclas+',',b.num)-b.num)
into #1 
from INSERTED as a, nums as b 
where charindex(',',','+a.c_productsbclas,b.num)=b.num 


select 	b.num,a.c_userid,smallclass=substring(a.c_productssclas,b.num,charindex(',',c_productssclas+',',b.num)-b.num)
into #2 
from INSERTED as a, nums as b 
where charindex(',',','+a.c_productssclas,b.num)=b.num 

delete #1 where bigclass=''
delete #2 where smallclass=''
update #1 set num=(select count(1) from #1 where c_userid=t.c_userid and num<=t.num) from #1 as t 
update #2 set num=(select count(1) from #2 where c_userid=t.c_userid and num<=t.num) from #2 as t


select 
	[company]=a.c_name,a.c_userid,a.bigclass,b.smallclass,a.num
into #3
from 
	#1 as a
left join 
	#2 as b on a.c_userid=b.c_userid and a.num=b.num 
order by a.c_userid, a.num


--刪除大小類其中有改動的
delete a
from 
	Asean_company_sort a
join
	#3 a2 on a.c_userid=a2.c_userid
left join 
	#3 b on b.c_userid=a.c_userid and a.bclass=b.bigclass and b.smallclass=a.sclass
where
	b.c_userid  is null  

insert  Asean_company_sort(company,c_userid,bclass,sclass,sort)
select 
	a.*
from 	
	#3 a
left join
	Asean_company_sort  b on a.c_userid=b.c_userid and a.bigclass=b.bclass and a.smallclass=b.sclass
where
	b.c_userid is null

set nocount off

go


insert Asean_company 
select 3,201,',31,32,33,34',',3001,3002,3003,3004'
  union all select 4,301,',24,23,22,21',',2004,2003,2002,2001'

select * from Asean_company
select * from Asean_company_sort



update Asean_company set c_productsbclas=',13,14,15,16', c_productssclas=',4001,4002,4003,4004' where c_userid=201

select * from Asean_company
select * from Asean_company_sort

[中国风]

insert Asean_company 
select 3,201,',31,32,33,34',',3001,3002,3003,3004'
  union all select 4,301,',24,23,22,21',',2004,2003,2002,2001'

select * from Asean_company
select * from Asean_company_sort



update Asean_company set c_productsbclas=',31,14,15,16', c_productssclas=',3001,4002,4003,4004' where c_userid=201--31大類和3001不變

select * from Asean_company
select * from Asean_company_sort
id          c_name                                                                                               c_userid    c_productsbclas                                                                                      c_productssclas
----------- ---------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1           3                                                                                                    201         ,31,14,15,16                                                                                         ,3001,4002,4003,4004
2           4                                                                                                    301         ,24,23,22,21                                                                                         ,2004,2003,2002,2001

(2 個資料列受到影響)

id          company                                                                                              c_userid    bclass     sclass     sort
----------- ---------------------------------------------------------------------------------------------------- ----------- ---------- ---------- --------------------
1           3                                                                                                    201         31         3001       1
9           3                                                                                                    201         14         4002       2
10          3                                                                                                    201         15         4003       3
11          3                                                                                                    201         16         4004       4
5           4                                                                                                    301         24         2004       1
6           4                                                                                                    301         23         2003       2
7           4                                                                                                    301         22         2002       3
8           4                                                                                                    301         21         2001       4

(8 個資料列受到影響)

[中国风]

--sort是在觸發器中生成
--以下部份不指定時由默認自生成,樓主自定義
insert  Asean_company_sort(company,c_userid,bclass,sclass,sort)--不指定sort
select 
    a.* --指定列
from     
    #3 a
left join
    Asean_company_sort  b on a.c_userid=b.c_userid and a.bigclass=b.bclass and a.smallclass=b.sclass
where
    b.c_userid is null

[flush_520]

更新看你的测试没问题了

下班落 明天再来测试 非常感谢！
insert Asean_company
select 3,201,',31,31,33,34,',',3001,3002,3003,3004,'

大小类结尾都有逗号
我希望的结果是
ID COMPANY C_USERID BCLASS SCLASS SORT
1 3 201 31 3001 1
2 3 201 31 3002 2
3 3 201 33 3003 3
4 3 201 34 3004 4
5 3 201 31 null 5
6 3 201 33 null 6
7 3 201 34 null 7


也就是每个大类都生成单独一条信息，小类留空，且不重复（比如大类为31的有两个，但只生成一条）

[中国风]

use test
go
if object_id('nums') is not null drop table nums
select top 1000 num=identity(int,1,1) into nums from syscolumns,sysobjects
go
alter table nums add constraint pk_nums primary key (num)
go


create table Asean_company(id int identity,c_name nvarchar(100), c_userid int, c_productsbclas nvarchar(100), c_productssclas nvarchar(100))
create table Asean_company_sort(id int identity,company nvarchar(100),c_userid int,bclass nvarchar(10), sclass nvarchar(10),sort bigint)

go

if object_id('up_company_sort') is not null
    drop trigger up_company_sort
go
create trigger up_company_sort on Asean_company 
for insert,update,delete
as
set nocount on
if not exists(select 1 from inserted)--判斷刪除
    begin
        delete b from     Asean_company_sort  as b where exists     (select 1 from DELETED where c_userid=b.c_userid)
        return
    end

select b.num,a.c_name,a.c_userid,bigclass=substring(a.c_productsbclas,b.num,charindex(',',c_productsbclas+',',b.num)-b.num)
into #1 
from INSERTED as a, nums as b 
where charindex(',',','+a.c_productsbclas,b.num)=b.num 


select     b.num,a.c_userid,smallclass=substring(a.c_productssclas,b.num,charindex(',',c_productssclas+',',b.num)-b.num)
into #2 
from INSERTED as a, nums as b 
where charindex(',',','+a.c_productssclas,b.num)=b.num 

delete #1  from #1 a where bigclass='' and not exists(select 1 from #1 where c_userid=a.c_userid and num<a.num)
delete #2 from #2 a where smallclass='' and not exists(select 1 from #2 where c_userid=a.c_userid and num<a.num)

update #1 set num=(select count(1) from #1 where c_userid=t.c_userid and num<=t.num) from #1 as t 
update #2 set num=(select count(1) from #2 where c_userid=t.c_userid and num<=t.num) from #2 as t


select     [company]=a.c_name,a.c_userid,[bigclass]=nullif(a.bigclass,''),[smallclass]=nullif(b.smallclass,''),a.num
into #3
from     #1 as a left join     #2 as b on a.c_userid=b.c_userid and a.num=b.num 
order by a.c_userid, a.num


--刪除大小類其中有改動的
delete a
from 
    Asean_company_sort a
join
    #3 a2 on a.c_userid=a2.c_userid
left join 
    #3 b on b.c_userid=a.c_userid and isnull(a.bclass,'')=isnull(b.bigclass,'') and isnull(b.smallclass,'')=isnull(a.sclass,'')
where
    b.c_userid  is null  

insert  Asean_company_sort(company,c_userid,bclass,sclass,sort)
select 
    a.*
from     
    #3 a
left join
    Asean_company_sort  b on a.c_userid=b.c_userid 
	and isnull(a.bigclass,'')=isnull(b.bclass,'') and isnull(a.smallclass,'')=isnull(b.sclass,'')
where
    b.c_userid is null

set nocount off

go
--truncate table Asean_company
--truncate table Asean_company_sort


insert Asean_company 
select 3,201,',31,32,33,34',',3001,3002,3003,3004'
  union all select 4,301,',24,23,22,21',',2004,2003,2002,2001'

select * from Asean_company
select * from Asean_company_sort



update Asean_company set c_productsbclas=',31,32,,', c_productssclas=',,,3003,3004' where c_userid=201--这样更新

select * from Asean_company
select * from Asean_company_sort


id          c_name                                                                                               c_userid    c_productsbclas                                                                                      c_productssclas
----------- ---------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1           3                                                                                                    201         ,31,32,,                                                                                             ,,,3003,3004
2           4                                                                                                    301         ,24,23,22,21                                                                                         ,2004,2003,2002,2001

(2 行受影响)

id          company                                                                                              c_userid    bclass     sclass     sort
----------- ---------------------------------------------------------------------------------------------------- ----------- ---------- ---------- --------------------
9           3                                                                                                    201         31         NULL       1
10          3                                                                                                    201         32         NULL       2
11          3                                                                                                    201         NULL       3003       3
12          3                                                                                                    201         NULL       3004       4
5           4                                                                                                    301         24         2004       1
6           4                                                                                                    301         23         2003       2
7           4                                                                                                    301         22         2002       3
8           4                                                                                                    301         21         2001       4

(8 行受影响)

[中国风]

没有改变的大类或小类
if update（column）
或
instead of --類型

[中国风]

原貼？
---------
在觸發器里拆分字符。。05可用xml/也可用CTE替換

[flush_520]

http://topic.csdn.net/u/20080527/14/30f408a3-947c-4f65-bb7e-97f945bd7683.html
原帖在此 sql2000

[中国风]

Asean_company_sort里面有个手工排序字段也就废了?
--------------------
是指?

[flush_520]

非常感谢你的付出！此问题我再慢慢研究，结帖了