--try create trigger changecode on fclass for update as IF UPDATE ([分类编号]) begin update r set 分类编号 = t.分类编号, 商品编号=t.分类编号+right(商品编号,4) from sinfo r join inserted t on r.分类编号=t.分类编号end
--用触发器 create table tb(分类编号 varchar(10), 商品编号 varchar(20)) insert into tb values('AAA' , 'AAA0001') insert into tb values('BBB' , 'BBB0002') go --修改前的数据 select * from tb /* 分类编号 商品编号 ---------- -------------------- AAA AAA0001 BBB BBB0002(所影响的行数为 2 行) */go create trigger mytrig on tb for update as begin declare @s1 as varchar(10) declare @s2 as varchar(10) select @s1 = 分类编号 from deleted select @s2 = 分类编号 from inserted update tb set 商品编号 = replace(商品编号 , @s1 , @s2) end goupdate tb set 分类编号 = 'CCC' where 分类编号 = 'AAA' --修改后的数据 select * from tb /*分类编号 商品编号 ---------- -------------------- CCC CCC0001 BBB BBB0002(所影响的行数为 2 行) */drop table tb
--> 测试数据:[sinfo] if object_id('[sinfo]') is not null drop table [sinfo] create table [sinfo]([分类编号] varchar(3),[商品编号] varchar(7)) insert [sinfo] select 'AAA','AAA0001' union all select 'BBB','BBB0002' --> 测试数据:[fclass] if object_id('[fclass]') is not null drop table [fclass] create table [fclass]([分类编号] varchar(3)) insert [fclass] select 'AAA' union all select 'BBB' --创建触发器 create trigger changecode on fclass for update as IF UPDATE ([分类编号]) begin update r set 分类编号 = t.分类编号, 商品编号=t.分类编号+right(商品编号,4) from sinfo r, inserted t,deleted h where r.分类编号=h.分类编号end --修改 update [fclass] set [分类编号] = 'CCC' where [分类编号] = 'AAA'select * from [sinfo] --结果 ------------------------- CCC CCC0001 BBB BBB0002
create trigger yes on sinfo for update as declare @flbh varchar(20),@spbh varchar(20) declare yb1 cursor for select 分类编号,商品编号 from sinfo open yb1 fetch next from yb1 into @flbh,@spbh while @@fetch_status=0 begin update sinfo set 商品编号=@flbh+substring(@spbh,4,4) where current of yb1 fetch next from yb1 into @flbh,@spbh end close yb1 deallocate yb1
1.upate fclass set 分类编号='SSS',商品编号=replace(商品编号,分类编号,SSS') 2.update fclass set 商品编号='SSS'+SUBSTRING(商品编号,4,length(商品编号))
set @s='NewAAA'
upate sinfo set 分类编号=@s,商品编号=replace(商品编号,分类编号,@s)
--go
create table tb(a nvarchar(10),b nvarchar(10))
insert into tb select
'AAA' ,'AAA0001' union select
'BBB' ,'BBB0002' declare @new as nvarchar(10)
set @new='888'update tb set a=@new,b=replace(b,a,@new) where a='AAA'select * from tba b
---------- ----------
888 8880001
BBB BBB0002(所影响的行数为 2 行)
分类编号 商品编号
AAA AAA0001
BBB BBB0002 update 表名 set 分类编号='sss',商品编号='sss'+right(商品编号,4)
商品编号字段在哪个表里面?是不是只在sinfo表里面??
分类编号这个字段在fclass表里面这样的话 需要用触发器如果sinfo表里面有商品编号,分类编号这两个字段 可以用他们上面的语句
create trigger changecode on fclass
for update
as
IF UPDATE ([分类编号])
begin update r set 分类编号 = t.分类编号,
商品编号=t.分类编号+right(商品编号,4)
from sinfo r join inserted t on r.分类编号=t.分类编号end
create table tb(分类编号 varchar(10), 商品编号 varchar(20))
insert into tb values('AAA' , 'AAA0001')
insert into tb values('BBB' , 'BBB0002')
go
--修改前的数据
select * from tb
/*
分类编号 商品编号
---------- --------------------
AAA AAA0001
BBB BBB0002(所影响的行数为 2 行)
*/go
create trigger mytrig on tb for update
as
begin
declare @s1 as varchar(10)
declare @s2 as varchar(10)
select @s1 = 分类编号 from deleted
select @s2 = 分类编号 from inserted
update tb set 商品编号 = replace(商品编号 , @s1 , @s2)
end
goupdate tb set 分类编号 = 'CCC' where 分类编号 = 'AAA'
--修改后的数据
select * from tb
/*分类编号 商品编号
---------- --------------------
CCC CCC0001
BBB BBB0002(所影响的行数为 2 行)
*/drop table tb
if object_id('[sinfo]') is not null drop table [sinfo]
create table [sinfo]([分类编号] varchar(3),[商品编号] varchar(7))
insert [sinfo]
select 'AAA','AAA0001' union all
select 'BBB','BBB0002'
--> 测试数据:[fclass]
if object_id('[fclass]') is not null drop table [fclass]
create table [fclass]([分类编号] varchar(3))
insert [fclass]
select 'AAA' union all
select 'BBB'
--创建触发器
create trigger changecode on fclass
for update
as
IF UPDATE ([分类编号])
begin update r set 分类编号 = t.分类编号,
商品编号=t.分类编号+right(商品编号,4)
from sinfo r, inserted t,deleted h
where r.分类编号=h.分类编号end --修改
update [fclass]
set [分类编号] = 'CCC'
where [分类编号] = 'AAA'select * from [sinfo]
--结果
-------------------------
CCC CCC0001
BBB BBB0002
AAA 0001
BBB 0002然后通过查询来显示
AAA AAA0001
BBB BBB0002
create trigger yes on sinfo
for update
as
declare @flbh varchar(20),@spbh varchar(20)
declare yb1 cursor for select 分类编号,商品编号 from sinfo
open yb1
fetch next from yb1 into @flbh,@spbh
while @@fetch_status=0
begin
update sinfo set 商品编号=@flbh+substring(@spbh,4,4)
where current of yb1
fetch next from yb1 into @flbh,@spbh
end
close yb1
deallocate yb1
2.update fclass set 商品编号='SSS'+SUBSTRING(商品编号,4,length(商品编号))