sql server编写触发器

最新推荐文章于 2024-08-18 15:44:40 发布

风夏了白雪yi

最新推荐文章于 2024-08-18 15:44:40 发布

阅读量699

点赞数

分类专栏：数据库开发文章标签： sql server

本文链接：https://blog.csdn.net/snowYing97/article/details/70053571

版权

数据库开发专栏收录该内容

4 篇文章 0 订阅

订阅专栏

这篇博客探讨了如何在SQL Server中编写触发器，最初尝试根据C的出现次数作为栏目数量进行截取，但发现这种方法存在局限。随后，作者改写了触发器脚本，实现了更简洁且能应对多个栏目的高效处理。

摘要由CSDN通过智能技术生成

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

具体需求如下：现有一张表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  
                end
end