sql server编写触发器

来源：互联网发布：linux 进程stdio 编辑：程序博客网时间：2024/06/05 09:46

最近生产环境上出现了些数据层面的问题，以前关联表的数据用传输工具进行传输，这样时间上会有差异。为了解决这一问题，现要求用触发器实现。

具体需求如下：现有一张表treport，包含reportid,classids等字段，其中classids由一个或多个栏目值组成。而每个栏目值都是以C开头，每两个栏目值中间用','间隔，即使只有一个栏目值，也以','结尾。现要求往treport表插入一条数据的同时，往关联表tclasstree_report也插入相关数据，要求只插入reportid，classid字段。其中classid是classids中的每个单独栏目值，即classids有两个栏目值，就分别获取两个栏目，往tclasstree_report表中插入两条数据，以此类推……

需求示例如下：
treport表数据如下：
ID ReportID ClassIDs
1132877 R5035181 C1000,
1132881 R5035173 C732,C113,C751,
1132882 R5035174 C732,C69,
1132883 R5035175 C732,C123,C124,
1132884 R5035176 C732,C139,C1143,

tclasstree_report表数据如下：
ID ReportID   ClassID
7185280 R5035173   C732
7185281 R5035173   C113
7185282 R5035173   C751
7185283 R5035174   C732
7185284 R5035174   C69
7185285 R5035175   C732
7185286 R5035175   C123
7185287 R5035175   C124
7185288 R5035176   C732
7185289 R5035176   C139
7185290 R5035176   C1143
7185270 R5035181   C1000

刚开始的时候，想着以C出现的次数为栏目个数，然后依次截取各个栏目，编写触发器脚本如下：

create trigger TRIG_TREPORT_TCLASSTREE on treport for insert as  begin    declare @len int;    select @len=len(classids)-len(replace(classids,'C','')) from inserted;    if (@len=1)      begin          insert into tclasstree_report(reportid,classid)          select reportid,left(classids,len(classids)-1) from inserted;      end;    else if (@len=2)      begin          insert into tclasstree_report(reportid,classid)          select reportid,substring(classids,1,CHARINDEX(',',ClassIDs,1)-1) from inserted          union          select reportid,substring(classids,CHARINDEX(',',ClassIDs,1)+1,len(classids)-CHARINDEX(',',ClassIDs,1)-1) from inserted      end    else if (@len=3)      begin          insert into tclasstree_report(reportid,classid)          select  reportid,substring(classids,1,CHARINDEX(',',ClassIDs,1)-1) from inserted          union          select reportid,substring(classids,CHARINDEX(',',ClassIDs,1)+1,CHARINDEX(',',ClassIDs,CHARINDEX(',',ClassIDs,1)+1)-CHARINDEX(',',ClassIDs,1)-1) from inserted          union          select reportid,substring(classids,charindex(',',classids,CHARINDEX(',',classids,1)+1)+1,len(classids)-charindex(',',classids,CHARINDEX(',',classids,1)+1)-1) from inserted      end    else if (@len=4)      begin          insert into tclasstree_report(reportid,classid)          select reportid,substring(classids,1,CHARINDEX(',',ClassIDs,1)-1) from inserted          union          select reportid,substring(classids,CHARINDEX(',',ClassIDs,1)+1,CHARINDEX(',',ClassIDs,CHARINDEX(',',ClassIDs,1)+1)-CHARINDEX(',',ClassIDs,1)-1) from inserted          union           select reportid,substring(classids,charindex(',',classids,CHARINDEX(',',classids,1)+1)+1,                 charindex(',',classids,charindex(',',classids,CHARINDEX(',',classids,1)+1)+1)-charindex(',',classids,CHARINDEX(',',classids,1)+1)-1) from inserted          union          select reportid,substring(classids,charindex(',',classids,charindex(',',classids,CHARINDEX(',',classids,1)+1)+1)+1,                 len(classids)-charindex(',',classids,charindex(',',classids,CHARINDEX(',',classids,1)+1)+1)-1) from inserted      end  end

后觉得此方法不妥，若栏目个数大于四个，则没法实现插入操作，且想要实现起来更加困难。于是就对其进行了改写，改写后不仅代码简化了很多，而且再多的栏目也能快速处理。具体实现如下：

alter trigger TRIG_TREPORT_TCLASSTREE on tReport for insert as   begin     declare @s varchar(100);     select @s=classids from inserted;     while (charindex(',',@s)<>0)     begin         --第一个','之前的字符串         insert into tclasstree_report(reportid,classid)         select reportid,substring(@s,1,charindex(',',@s)-1) from inserted;         --将第一个','后面的字符串重新赋给@s         set @s=stuff(@s,1,charindex(',',@s),'')         --最后一个字符串         if (charindex(',',@s)=1)             begin                 insert into tclasstree_report(reportid,classid)                 select reportid,substring(@s,1,charindex(',',@s)-1) from inserted;              end     end end

后来，需求再次变更，要求当treport表的classids字段根栏目为C732时，若只有两个栏目，则往tclasstree_report表插入两条数据；若classids有三个栏目，则跳过中间一个栏目，往tclasstree_report表插入第一个和第三个栏目。当treport表的classids字段根栏目不为C732时，插入规则和上面一样。

需求示例如下：
treport表数据如下：
ID ReportID ClassIDs
1132877 R5035181 C1000,
1132881 R5035173 C732,C113,C751,
1132882 R5035174 C732,C69,
1132883 R5035175 C732,C123,C124,
1132884 R5035176 C732,C139,C1143,

tclasstree_report表数据如下：
ID ReportID   ClassID
7185280 R5035173   C732
7185282 R5035173   C751
7185283 R5035174   C732
7185284 R5035174   C69
7185285 R5035175   C732
7185287 R5035175   C124
7185288 R5035176   C732
7185290 R5035176   C1143
7185270 R5035181   C1000

实现如下：

alter trigger TRIG_TREPORT_TCLASSTREE on TReport for insert as  begin     declare @s varchar(100);     select @s=classids from inserted;     if (CHARINDEX('C732',@s,1)=0)          begin               while (charindex(',',@s)<>0)                    begin                         --第一个','之前的字符串                         insert into tclasstree_report(reportid,classid)                         select reportid,substring(@s,1,charindex(',',@s)-1) from inserted;                         --将第一个','后面的字符串重新赋给@s                         set @s=stuff(@s,1,charindex(',',@s),'')                         --最后一个字符串                         if (charindex(',',@s)=1)                            begin                                  insert into tclasstree_report(reportid,classid)                                  select reportid,substring(@s,1,charindex(',',@s)-1) from inserted;                              end                    end           end         else if (CHARINDEX('C732',@s,1)<>0)                begin                     if (LEN(@s)-LEN(replace(@s,'C',''))=2)                           begin                               --第一个','之前的字符串                               insert into tclasstree_report(reportid,classid)                               select reportid,substring(@s,1,charindex(',',@s)-1) from inserted;                               --将第一个','后面的字符串重新赋给@s                               set @s=stuff(@s,1,charindex(',',@s),'')                               --第二个字符串                               insert into tclasstree_report(reportid,classid)                               select reportid,substring(@s,1,charindex(',',@s)-1) from inserted;                             end                       else if (LEN(@s)-LEN(replace(@s,'C',''))=3)                            begin                                --第一个','之前的字符串                                insert into tclasstree_report(reportid,classid)                                select reportid,substring(@s,1,charindex(',',@s)-1) from inserted;                                --第三个栏目                                insert into tclasstree_report(reportid,classid)                                select reportid,substring(@s,charindex(',',@s,CHARINDEX(',',@s,1)+1)+1,len(@s)-                                 charindex(',',@s,CHARINDEX(',',@s,1)+1)-1) from inserted;                            end                  endend

0 0