知方可补不足~sqlserver中触发器的使用

回到目录 触发器 在过去的10年中，即存储过程和ado.net称霸江湖期间是那么的重要，而现在，trigger显得不是那么必要的，我们很少将复杂的业务写在SQL里，当然也会没有机会写到trigger里了，可对于数据库本身来说，这东西还是要说说的，尤其是一些特殊场合，

回到目录

触发器在过去的10年中，即存储过程和ado.net称霸江湖期间是那么的重要，而现在，trigger显得不是那么必要的，我们很少将复杂的业务写在SQL里，当然也会没有机会写到trigger里了，可对于数据库本身来说，这东西还是要说说的，尤其是一些特殊场合，看看下面的触发器使用场合：

这种结构很有意思，在SQLSERVER中实现两个数据库实时同步的方法有很多，比较简单的是“发布与订阅”，但说实话，这东西是有风显的，只要一个服务重新启动，它的同步数据就被删除了，即，后台数据库数据被删除，需要重新进行同步，感觉挺危险的，今天主要说的是一种传统的方法实现某些表数据的同步，即使用触发器实现数据的同步。

我们以Category表为例，前台数据库名为[background],后台数据库名为[background_copy]，当category表有数据插入时，[background_copy]表的数据自动实现插入，看一下代码部分吧：

<span>USE</span> <span>[</span><span>background</span><span>]</span>
<span>GO</span>
<span>/*</span><span>***** Object:  Trigger [dbo].[Trigger_Categoryinsert]    Script Date: 09/10/2013 15:31:41 *****</span><span>*/</span>
<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
<span>ALTER</span> <span>TRIGGER</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Trigger_Categoryinsert</span><span>]</span> <span>ON</span> <span>[</span><span>background</span><span>]</span>.<span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Category</span><span>]</span><span>
    AFTER </span><span>INSERT</span>
<span>AS</span>
    <span>SET</span> <span>IDENTITY_INSERT</span> background_copy.<span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Category</span><span>]</span> <span>ON</span> 
    <span>INSERT</span>  <span>INTO</span><span> background_copy.dbo.category
            ( </span><span>[</span><span>ID</span><span>]</span><span> ,
              </span><span>[</span><span>ParentID</span><span>]</span><span> ,
              </span><span>[</span><span>Level</span><span>]</span><span> ,
              </span><span>[</span><span>Name</span><span>]</span><span> ,
              </span><span>[</span><span>IsParent</span><span>]</span><span> ,
              </span><span>[</span><span>CreateDate</span><span>]</span><span> ,
              </span><span>[</span><span>Sortable</span><span>]</span><span>
            )
            </span><span>SELECT</span>  <span>[</span><span>ID</span><span>]</span><span> ,
                    </span><span>[</span><span>ParentID</span><span>]</span><span> ,
                    </span><span>[</span><span>Level</span><span>]</span><span> ,
                    </span><span>[</span><span>Name</span><span>]</span><span> ,
                    </span><span>[</span><span>IsParent</span><span>]</span><span> ,
                    </span><span>[</span><span>CreateDate</span><span>]</span><span> ,
                    </span><span>[</span><span>Sortable</span><span>]</span>
            <span>FROM</span><span>    INSERTED
    </span><span>SET</span> <span>IDENTITY_INSERT</span> background_copy.<span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Category</span><span>]</span> <span>OFF</span>

我们可以看到，代码中使用SET IDENTITY_INSERT TableName ON/OFF命令，即，你的数据表主键是自增的，在同步时，需要去掉后台表的自增特性，这样才可以与前台的自增主键保持一致。

下面再看一下触发器的更新，使用场合介绍：当category表的name字段有更新后，自动同步到background_copy.[dbo].[Category] 表的name字段

 

<span>USE</span> <span>[</span><span>background</span><span>]</span>
<span>GO</span>
<span>/*</span><span>***** Object:  Trigger [dbo].[Trigger_CategoryUpdate]    Script Date: 09/10/2013 16:06:31 *****</span><span>*/</span>
<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
<span>ALTER</span> <span>TRIGGER</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Trigger_CategoryUpdate</span><span>]</span> <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Category</span><span>]</span><span>
    AFTER </span><span>UPDATE</span>
<span>AS</span>
    <span>IF</span> <span>UPDATE</span><span>(Name) 
        </span><span>BEGIN</span>
            <span>UPDATE</span>  <span>[</span><span>background_copy</span><span>]</span>.<span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Category</span><span>]</span>
            <span>SET</span>     <span>[</span><span>Name</span><span>]</span> <span>=</span> ( <span>SELECT</span><span>   name
                               </span><span>FROM</span><span>     INSERTED
                             )
            </span><span>WHERE</span>   ID <span>=</span> ( <span>SELECT</span><span>   id
                           </span><span>FROM</span><span>     INSERTED
                         )
        </span><span>END</span>
    

 再来看一个更新表所有字段的，事实上就是把原来的记录删除，把新修改的记录插入就可以了，要求你注意的是自增主键的开关问题，看代码：

<span>USE</span> <span>[</span><span>BACKGROUND_COPY</span><span>]</span>
<span>GO</span>
<span>/*</span><span>***** OBJECT:  TRIGGER [DBO].[TRIGGER_CATEGORYUPDATE]    SCRIPT DATE: 09/13/2013 18:01:00 *****</span><span>*/</span>
<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
<span>ALTER</span> <span>TRIGGER</span> <span>[</span><span>DBO</span><span>]</span>.<span>[</span><span>TRIGGER_CATEGORYUPDATE</span><span>]</span> <span>ON</span> <span>[</span><span>DBO</span><span>]</span>.<span>[</span><span>CATEGORY</span><span>]</span><span>
    AFTER </span><span>UPDATE</span>
<span>AS</span>
    <span>DELETE</span><span>  BACKGROUND_COPY2.DBO.CATEGORY
    </span><span>WHERE</span>   ID <span>=</span> ( <span>SELECT</span><span>   ID
                   </span><span>FROM</span><span>     INSERTED
                 ) 

    </span><span>SET</span> <span>IDENTITY_INSERT</span> BACKGROUND_COPY2.DBO.CATEGORY <span>ON</span>
    <span>INSERT</span>  <span>INTO</span><span> BACKGROUND_COPY2.DBO.CATEGORY
            ( ID ,
              PARENTID ,
              </span><span>LEVEL</span><span> ,
              NAME ,
              ISPARENT ,
              CREATEDATE ,
              SORTABLE
            )
            </span><span>SELECT</span>  <span>*</span>
            <span>FROM</span><span>    INSERTED
    </span><span>SET</span> <span>IDENTITY_INSERT</span>  BACKGROUND_COPY2.DBO.CATEGORY <span>OFF</span>

 

 看一下删除功能的触发器：

<span>USE</span> <span>[</span><span>BACKGROUND_COPY</span><span>]</span>
<span>GO</span>
<span>/*</span><span>***** OBJECT:  TRIGGER [DBO].[TRIGGER_CATEGORYDELETE]    SCRIPT DATE: 09/13/2013 17:58:27 *****</span><span>*/</span>
<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
<span>ALTER</span> <span>TRIGGER</span> <span>[</span><span>DBO</span><span>]</span>.<span>[</span><span>TRIGGER_CATEGORYDELETE</span><span>]</span> <span>ON</span> <span>[</span><span>DBO</span><span>]</span>.<span>[</span><span>CATEGORY</span><span>]</span><span>
    AFTER </span><span>DELETE</span>
<span>AS</span>
    <span>DELETE</span><span>  BACKGROUND_COPY2.DBO.CATEGORY
    </span><span>WHERE</span>   ID <span>=</span> ( <span>SELECT</span><span>   ID
                   </span><span>FROM</span><span>     DELETED
                 ) 
                 </span>

 

OK，现在我们的category表就可以实现自动同步了，呵呵。

回到目录