• trigger中insert动作的测试


    Trigger为默认事务

    测试环境:sql server 2008 r2

    对象:DevList表

    目标:确定trigger在数据库中有数据变化时是一次一批一批执行还是,按每条触发执行

    测试需求:

    DevList的触发器

     

    CREATE TRIGGER dbo.Test

       ON  DevList

       AFTER insert

    AS

    BEGIN

        SET NOCOUNT ON;

     

        -- Insert statements for trigger here

        --修改Table_Test数量+1

        update Table_Test set [No]=[No]+1

    END

    GO

    表Table_Test:

    CREATE TABLE [dbo].[Table_Test](

        [No] [int] NULL

    ) ON [PRIMARY]

    用例:

    一、使用insert语句想DevList中插入数据,本次不用事务,不用存储过程,同时执行10条insert语句

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.45','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.46','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.47','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.48','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.49','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.50','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.51','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.52','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.53','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.54','aaaaa','bbbbb','ccccc',0,0)

    二、在存储过程中使用10条insert语句,本次不用事务

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author:         <Author,,Name>

    -- Create date: <Create Date,,>

    -- Description:    <Description,,>

    -- =============================================

    CREATE PROCEDURE dbo.NoTranTest

          

    AS

    BEGIN

         -- SET NOCOUNT ON added to prevent extra result sets from

         -- interfering with SELECT statements.

         SET NOCOUNT ON;

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.55','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.56','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.57','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.58','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.59','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.60','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.61','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.62','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.63','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.64','aaaaa','bbbbb','ccccc',0,0)

     

     

    END

    GO

    三、在存储过程中使用10条insert语句,本次使用事务

     

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

     

    CREATE PROCEDURE dbo.TranTest

          

    AS

    BEGIN

         -- SET NOCOUNT ON added to prevent extra result sets from

         -- interfering with SELECT statements.

         SET NOCOUNT ON;

        

             ---开启事务

    begin tran

       --错误扑捉机制,看好啦,这里也有的。并且可以嵌套。

    begin try 

        INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.75','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.76','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.77','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.78','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.79','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.80','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.81','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.82','aaaaa','bbbbb','ccccc',0,0)

         INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.83','aaaaa','bbbbb','ccccc',0,0)

    INSERT INTO [DCMHR].[dbo].[DevList]([ip],[subnet],[gateway],[script],[status],[reccount]) VALUES ('190.10.24.84','aaaaa','bbbbb','ccccc',0,0)

      

    end try

     

     begin catch

        --select Error_number() as ErrorNumber,  --错误代码

        --       Error_severity() as ErrorSeverity,  --错误严重级别,级别小于try catch 捕获不到

        --       Error_state() as ErrorState ,  --错误状态码

        --       Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。

        --       Error_line() as ErrorLine,  --发生错误的行号

        --       Error_message() as ErrorMessage  --错误的具体信息

        if(@@trancount>0) --全局变量@@trancount,事务开启此值+1,他用来判断是有开启事务

          begin

           rollback tran  ---由于出错,这里回滚到开始,

           

          end

     end catch

     if(@@trancount>0)

         begin

         commit tran  --如果成功Lives表中,将会有条数据。

         end

     

     

    END

    GO

    执行测试之前清空DevList表中数据,Table_Test表中第一栏,第一个格设为0

    执行情况:

    执行用例中一选项,查看Table_Test结果10

    执行用例中二选项

    Exec NoTranTest

    查看Table_Test结果20

    执行用例三选项

    Exec TranTest

    查看Table_Test结果30

  • 相关阅读:
    web服务器12 中间件函数
    web11 路由
    web服务器15 jsonp格式接口
    web服务器14 cors跨域资源共享 报错及解决方案
    小程序弹窗(模态框)遮罩层 弹窗右上角按钮关闭
    数据库1 mysq数据库的安装全套
    MySQL数据库3 的 where语句 and 和 or运算符
    MySQL数据库2 增删改查
    MySQL分组排序取每组第一条
    IDEA频繁闪退解决
  • 原文地址:https://www.cnblogs.com/netqq/p/4140057.html
Copyright © 2020-2023  润新知