求教关于一个问题的触发器编写

Question

现有某表结构如下：
F1      F2     F3     F4       F5
A       B                      A\B
A       B      C      D        A\B\C\D\
A       B      C               A\B\C写一个触发器，自动填写F5字段， 其中F5=F1\F2\F3\F4\,当F某个字段值为空时，相应的位置不应出现\符号

Answer 1

--就算列
if object_id('tempdb..#')is not null drop table #
go
create table #(F1 varchar(10),
               F2 varchar(10),
               F3 varchar(10),
               F4 varchar(10),
               F5 as replace(isnull(F1,'')
                     +'\'+isnull(F2,'')
                     +'\'+isnull(F3,'')
                     +'\'+isnull(F4,''),'\\',''))
insert # select 'A','B','',null
insert # select 'A','B','C','D'
insert # select 'A','B','C',''
select * from #
/*
F1         F2         F3         F4         F5
---------- ---------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A          B                     NULL       A\B
A          B          C          D          A\B\C\D
A          B          C                     A\B\C\(3 行受影响)
*/

Answer 2

少考虑好多情况的
譬如：F1 F2 F3 F4都为空的情况，那么就没有‘\’的，
还有两端的‘\’也需要处理掉

Answer 3

if object_id('tempdb..#')is not null drop table #
go
create table #(F1 varchar(10),
               F2 varchar(10),
               F3 varchar(10),
               F4 varchar(10),
               F5 as left(
                      isnull(case when F1='' then null else F1 end+'\','')
                     +isnull(case when F2='' then null else F2 end+'\','')
                     +isnull(case when F3='' then null else F3 end+'\','')
                     +isnull(case when F4='' then null else F4 end+'\',''),
                     len(
                      isnull(case when F1='' then null else F1 end+'\','')
                     +isnull(case when F2='' then null else F2 end+'\','')
                     +isnull(case when F3='' then null else F3 end+'\','')
                     +isnull(case when F4='' then null else F4 end+'\',''))-1))
insert # select 'A','B',null,null
insert # select '',null,'C','D'
insert # select 'A','','C',''
select * from #
/*
F1         F2         F3         F4         F5
---------- ---------- ---------- ---------- --------------------------------------------
A          B          NULL       NULL       A\B
           NULL       C          D          C\D
A                     C                     A\C*/

Answer 4

可能格式化不好：F1 F2 F3 F4 F5
A B A\B\
A B C D A\B\C\D
A B C A\B\C

Answer 5

if object_id('[tb]') is not null 
drop table [tb]
go
create table [tb](
[F1] varchar(10),
[F2] varchar(10),
[F3] varchar(10),
[F4] varchar(10),
[F5] varchar(80)
)
gocreate trigger tri_insert_tb
on tb
for insert
as
begin
  update a
  set a.F5=stuff((
           case when len(isnull(i.F1,''))=0 then '' else '/'+i.F1 end
          +case when len(isnull(i.F2,''))=0 then '' else '/'+i.F2 end
          +case when len(isnull(i.F3,''))=0 then '' else '/'+i.F3 end
          +case when len(isnull(i.F4,''))=0 then '' else '/'+i.F4 end
          ),1,1,'')
  from tb a,inserted i
  where isnull(a.f1,'')=isnull(i.f1,'') 
  and isnull(a.f2,'')=isnull(i.f2,'') 
  and isnull(a.f3,'')=isnull(i.f3,'') 
  and isnull(a.f4,'')=isnull(i.f4,'')
end
goinsert [tb](F1,F2,F3,F4)
select 'A','B',null,null union all
select 'A','B','C','D' union all
select 'A','B','C',''
goselect * from tb
/**
F1         F2         F3         F4         F5
---------- ---------- ---------- ---------- --------------------------------
A          B          NULL       NULL       A/B
A          B          C          D          A/B/C/D
A          B          C                     A/B/C(3 行受影响)
**/

Answer 6

不需要触发器，直接用查询语句即可。
create table tb(F1 varchar(10),F2 varchar(10), F3 varchar(10), F4 varchar(10), F5 varchar(10))
insert into tb values('A', 'B', '' , '' ,'')
insert into tb values('A', 'B', 'C', 'D','')
insert into tb values('A', 'B', 'C', '' ,'')
go--查询
select f1,f2,f3,f4,f5=
       reverse(case when f4 <> '' or f4 <> null then f4 + '\' else '' end + 
       case when f3 <> '' or f3 <> null then f3 + '\' else '' end + 
       case when f2 <> '' or f2 <> null then f2 + '\' else '' end + 
       case when f1 <> '' or f1 <> null then f1 else '' end) 
from tb
/*
f1         f2         f3         f4         f5                                          
---------- ---------- ---------- ---------- ------------------------------------------- 
A          B                                A\B
A          B          C          D          A\B\C\D
A          B          C                     A\B\C（所影响的行数为 3 行）
*/--更新
update tb set f5 = reverse(case when f4 <> '' or f4 <> null then f4 + '\' else '' end + 
       case when f3 <> '' or f3 <> null then f3 + '\' else '' end + 
       case when f2 <> '' or f2 <> null then f2 + '\' else '' end + 
       case when f1 <> '' or f1 <> null then f1 else '' end)
select * from tb
/*
f1         f2         f3         f4         f5                                          
---------- ---------- ---------- ---------- ------------------------------------------- 
A          B                                A\B
A          B          C          D          A\B\C\D
A          B          C                     A\B\C（所影响的行数为 3 行）
*/drop table tb