SQL Server 触发器批量数据行处理_-小龙人的博客

相关文章推荐
成熟的匕首 · 只显示文档中所有的特定字段_51CTO博客_ ...· 1 年前 ·
读研的红薯 · 微信小程序JS报错：Uncaught ...· 1 年前 ·
火星上的单杠 · crontab每5秒执行一次-掘金· 1 年前 ·
SQL Server的触发器在数据批量处理时，只会触发一次触发器，后面的操作触发器全部失效，SQL Server执行语句并不是按行语句进行，所以在批量处理数据时，触发器需要特殊处理。
处理方式1：
在触发器内新建一张临时表，将所有数据暂存在临时表（建议加上一个自增主键）中，循环临时表来触发触发器。
下面是一个Demo，批量插入数据，判断Age是否大于150，若大于数据回滚。
USE [BlogDemo]
/****** Object:  Trigger [dbo].[Trigger_Insert]    Script Date: 2019/5/29 10:52:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER TRIGGER [dbo].[Trigger_Insert]
   ON  [dbo].[Person]
   AFTER INSERT
BEGIN
	 SET NOCOUNT ON;
     --新建一张临时表，并加入一个自增主键ID，便于后面的循环操作
	 Create table #temp(ID int IDENTITY(1,1) PRIMARY KEY, PersonID int,Name varchar(50),Age int, AddDate datetime)
	 Insert into #temp(PersonID, Name, Age, AddDate)
	 		Select ID, Name, Age, AddDate From inserted
	 Declare @i Int=1,@is Int=0
	 SELECT @is =MAX([ID]) FROM #temp
	 WHILE @i<@is
	 	BEGIN
	 		Declare @age int =0;
	 		Select @age=Age From #temp Where ID=@i
	 		IF(@age<150)
	 			Begin
	 				Insert into PersonLog(PersonID, Name, Age, AddDate)
	 				Select ID, Name, Age, AddDate From #temp Where ID=@i
	 			Begin
	 				print('年龄应小于150')
	 				rollback transaction
	 		SET @i=@i+1;
处理方式2：
     在触发器内使用游标来循环数据触发触发器。
     同样的Demo如下： 
USE [BlogDemo]
/****** Object:  Trigger [dbo].[Trigger_Insert]    Script Date: 2019/5/29 10:52:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER TRIGGER [dbo].[Trigger_Insert]
   ON  [dbo].[Person]
   AFTER INSERT
BEGIN
	SET NOCOUNT ON;
   --游标也可以实现行机触发器
	   Declare insert_cursor cursor Forward_Only static
		For Select ID,Name,Age,AddDate from inserted
		open insert_cursor
		Declare @Id int, @Name varchar(50), @Age int,@AddDate datetime
		Fetch From insert_cursor into @Id, @Name, @Age, @AddDate
		while @@FETCH_STATUS=0
		  Begin
			IF(@Age>150)
				 Begin
					print('年龄应小于150')
					Rollback transaction 
				Insert Into PersonLog(PersonID,Name,Age,AddDate)
				 VALUES
				(@Id, @Name, @Age, @AddDate)
			Fetch Next From insert_cursor into  @Id, @Name, @Age, @AddDate
		close insert_cursor	
		deallocate insert_cursor
Demo测试数据图：
 
                    ALTER TRIGGER [dbo].[Trigger_Insert]   ON  [dbo].[Person]   AFTER INSERTAS BEGIN		SET NOCOUNT ON;	Create table #temp(ID int IDENTITY(1,1) PRIMARY KEY, PersonID int,Name varchar(50),Age int, Ad...
				1、写一个自定义函数，该函数返回Northwind数据库中Employees表中HireDate介于两个指定日期之间的人员个数。
2、（1）编写和执行存储过程，将Northwind数据库中的Orders表的指定EmployeeID对应的所有定单的Freight（费用）加上一个指定的金额(注：存储过程带输入参数)；（2）在（1）基础上
，存储过程还必须返回被修改的定单数（分别以返回值形式和输出参数形式）；（3）查找上述存储过程的信息。
3、在数据库JXGL中的teachers中创建一个触发器，如果添加的教师的工资超过5000，则拒绝插入该记录。（假设每次只插入一条记录即可）。
  数据库JXGL自已去创建,本题作业所用到的表结构如下： 
  teachers(tno,tname,dept,pay)其中TNO代表教师编号，TNAME代表教师姓名，DEPT代表教师系别，PAY代表教师的工资。
				要注意：触发器的触发是操作，而不是针对行记录，这也是为什么我一次删除、新增很多数据时结果只有一条记录被触发出来，在面对一条sql语句删除了、新增了多条记录时需要使用游标进行操作
SQL Server
场景：三张表 On_Duty,On_Duty_Future,On_Duty_History1
在On_Duty中删除一条记录，就把数据插入到另外两张表中，数据库语句如下：
create
				SQLServer数据库设计规范 作者：佚名 厚朴教育来源：网络 点击数：1446 更新时间：2008-12-2 1 相关的设计规范: 1.1 采用有意义的字段名 尽可能地把字段描述的清楚些。当然，也别做过头了，比如CustomerShippingAddressStreetLine1 虽然很富有说明性，但没人愿意键入这么长的名字，具体尺度就在你的把握中。每个单词的首个字母要求用大写!!!不要用下划线来连接每个单词. 1.2 遵守3NF 标准3NF 规定： A.表内的每一个值都只能被表达一次。 B.表内的每一行都应该被唯一的标识(有唯一键)。 C.表内不应该存储依赖于其他键的非键信息。 1.3 小心保留词 要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突，比如，写的一个ODBC 连接程序里有个表，其中就用了DESC 作为说明字段名。后果可想而知！DESC 是DESCENDING 缩写后的保留词。表里的一个SELECT *语句倒是能用，但我得到的却是一大堆毫无用处的信息。 1.4 保持字段名和类型的一致性 在命名字段并为其指定数据类型的时候一定要保证一致性。假如字段在某个表中叫做"AgreeMentNumber"，你就别在另一个表里把名字改成"ref1"。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。记住，你干完自己的活了，其他人还要用你的数据库呢。 2 命名规则: 2.1 数据库(Database)的定义 数据库名称 = 数据库内容标识(首字大写) 2.2 表(Table)的定义 命名应尽量反映存储的数据内容。 表名前缀：以该表及与该表相关联的一系列表的内容而得到一个代表统一的标识 表名称 = 表名前缀 + 表内容标识(首字大写) 如ClientInfo相关联的一系的表以ci作为前缀， 即有表名：ciHistory,ciContact,ciBusiness 2.25 字段(Field)的定义 字段是数据库中的用途最广泛的，它的类型非常多，所以必须加类型前缀来标示它的类型。 字段名称 = 字段类型前缀 + 字段内容标识(首字大写) 2.2视图名 视图的名称 = "vw" + 视图内容标识(首字大写) 如 vUserPerm 2.3触发器名 触发类型触发标识 ----------------------------------- Insert i Delete d Update u 触发名= "tr" + 触发标识 + 相应的表名 如：triClient，trdClient。 2.4存储过程名 存储过程功能存储过程功能前缀 ---------------------------------------- 添加a 更新u 删除d 查询或其它o 存储过程名称 = 存储过程功能前缀 + 存储过程内容标识(首字大写) 如 spoChkUserPerm 2.5变量名 每个单词的首个字母为大写，如@EmployeeName。 2.6命名中其他注意事项 禁止使用中文命名!!!!! 命名都不得超过30个字符的系统限制,变量名的长度限制为29(不包括标识字符@)。 命名都采用英文字符,每个单词的首个字母要大写 2.7 SQL语句的编写规范 数据库中存储过程和触发器中涉及大量的SQL语句，对SQL语句的编写规范如下： 关键字大写：在SQL语句的编写中，凡是SQL语句的关键字一律大写，如：SELECT、ORDER BY、 GROUP BY、 FROM、WHERE、 UPDATE、 INSERT INTO、 SET、 BEGIN、 END ...... 2.8 Rule ru+Rule标识 2.9 主键 pk+表名+主键标识 2.10 外键 fk+表名+主表名+外键标识 2.11 索引 idx+字段标识 2.12 Default df+Default标识 3 编程结构和描述 SQL SERVER系统中，一个批处理是从客户传给服务器的一个完整的包，可以包含若干条SQL语句。批处理中的语句是作为一组去进行语法分析、编译和执行的。触发器、存储过程等数据对象则是将批处理永久化的方法。 3.1注释 注释可以包含在批处理中。在触发器、存储过程中包含描述性注释将大大增加文本的可读性和可维护性。本规范建议： 1、注释以英文为主。 实际应用中，发现以中文注释的SQL语句版本在英文环境中不可用。为避免后续版本执行过程中发生某些异常错误，建议使用英文注释。 2、注释尽可能详细、全面。 创建每一数据对象前，应具体描述该对象的功能和用途。 传入参数的含义应该有所说明。如果取值范围确定，也应该一并说明。取值有特定含义的变量（如boolean类型变量），应给出每个值的含义。 3、注释语法包含两种情况：单行注释、多行注释 单行注释：注释前有两个连字符（--）
				SQL Server 触发器是一种特殊的存储过程，它在特定的表上执行，并且在特定的 DML 事件（如插入、更新或删除）发生时自动触发。触发器可以用于强制实施业务规则、审核数据更改、记录数据更改历史记录等方面。
触发器可以分为两种类型：行触发器和语句触发器。行触发器在每一行上触发，而语句触发器只在整个语句完成时触发一次。
创建触发器需要指定以下内容：
1. 触发器名称
2. 触发器所在的表
3. 触发器的事件类型（INSERT、UPDATE 或 DELETE）
4. 触发器的触发时间（BEFORE 或 AFTER）
5. 触发器的类型（FOR EACH ROW 或 FOR EACH STATEMENT）
6. 触发器的触发条件（使用 WHERE 子句）
7. 触发器的代码
例如，以下是一个简单的行级触发器，用于在 Employee 表中插入新行时自动将创建日期设置为当前日期：
```sql
CREATE TRIGGER tr_Employee_Insert
ON Employee
AFTER INSERT
BEGIN
    UPDATE Employee SET CreateDate = GETDATE() WHERE EmployeeID IN (SELECT EmployeeID FROM inserted)