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
