sqlserver触发器的使用以及inserted和deleted详解

目录

一、背景

二、什么是sqlserver触发器

触发器的种类

三、触发器的使用

四、总结

---

一、背景

最近在项目中有需求是当人员表中有变动时（比如：增加人员、修改人员信息、删除人员信息）需要把这张表中的变动的信息同步到它对应的日志表中。那么如果用代码写逻辑的话在执行效率上会比较慢，正好sqlserver提供了触发器，我们可以利用触发器进行解决这个问题。

二、什么是sqlserver触发器

触发器是在对表进行插入、更新或删除操作时自动执行的特殊存储过程。触发器通常用于强制业务规则，触发器是一种高级约束，可以定义比CHECK约束更为复杂的约束：可以执行复杂的SQL语句（if/while/case）,可以引用其他表中的列。触发器定义在特定的表上，与表相关，自动触发执行，不能直接调用，是一个事务（可回滚）。

触发器的种类

触发器是在对表进行增、删、改操作才能够触发触发器。这里分为after（之后）和instead of（之前）触发。after触发器要求只有执行某一操作（insert、update、delete）之后触发器才能被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身，其优先级高于触发语句的执行。

三、触发器的使用

以下内容介绍after之后的触发器。以navicat工具为例。

首先我们需要监测哪个数据库就在哪个数据库创建触发器。比如：需要监听人员表中的数据变化，那么就需要在人员表中添加触发器。点击表的设计，找到触发器

在上方点击添加触发器，并设置触发器的名称以及触发的时间和在什么情况下触发。

设置完这些之后可以去写逻辑

 

 首先介绍一下sql触发器中两个表，inserted和deleted。可以理解为这两张表是临时表，它的表字段和在哪个表中创建触发器的字段是一致的，比如：我在人员管理表中添加了触发器，那么inserted表和deleted表中的字段是一致的。这两张表是系统在内存中创建的两张表，不会存储到数据库中，且这两张表是只读的，不能修改数据。当触发器完成工作之后，这两张表也会被删除

表操作	inserted表	deleted表
insert	存放新增的记录	无
update	存放新增的记录	存放更新前的数据
delete	无	存放新增的数据

举例：

BEGIN

-- 			if EXISTS (select * from deleted)    //判断deleted表中是否有数据
		declare @is_delete char(20)       //定义变量
		if EXISTS(
			select is_delete from inserted  
		)
		
		declare @userId varchar(20)
		declare @userName VARCHAR(20)
		declare @machineId VARCHAR(40)
		declare @operator_Id VARCHAR(20)
		declare @operator varchar(20)
		select @is_delete=is_delete,@userId=user_id,@userName=user_name,@machineId=machine_id,@operator_Id=update_user_id,@operator=update_user_name FROM inserted   //给变量赋值

		IF @is_delete=1  判断条件
		BEGIN
		insert into User_Management_Logs(user_id,user_name,act,machine_id,operator,operator_id) VALUES(@userId,@userName,'删除用户',@machineId,@operator,@operator_Id)  //在哪张表中插入数据

	END
END

四、总结

SQL Server触发器是一种数据库对象，它可以在特定的数据库操作（如插入、更新、删除）发生时自动执行相应的操作。触发器可以用于实现数据完整性、数据一致性、数据审计等需求。以下是对SQL Server触发器的总结：

1. 触发器类型：SQL Server触发器分为两种类型：AFTER触发器和INSTEAD OF触发器。AFTER触发器在数据库操作之后执行，而INSTEAD OF触发器在数据库操作之前执行。

2. 触发器事件：触发器可以与INSERT、UPDATE和DELETE操作关联。可以为每个事件创建一个独立的触发器，或者使用多个事件在同一个触发器中。

3. 触发器操作：触发器可以执行多种操作，如插入、更新、删除数据，或者执行其他SQL语句、存储过程等。可以根据需要编写相应的逻辑来实现所需的操作。

4. 触发器执行时机：触发器可以在行级别或语句级别执行。行级触发器在每一行操作时都会触发，而语句级触发器在整个操作完成后触发。

5. 触发器上下文：触发器可以访问和使用特定的上下文信息，如触发器事件中的新值和旧值，以及其他相关的数据库对象。可以使用这些上下文信息来编写触发器的逻辑。

6. 触发器的创建和管理：可以使用CREATE TRIGGER语句来创建触发器，并使用ALTER TRIGGER和DROP TRIGGER语句来修改和删除触发器。可以通过查询系统视图和表来管理触发器。

7. 触发器的性能考虑：触发器的执行会增加数据库操作的开销，因此需要考虑触发器的性能影响。可以通过合理设计触发器的逻辑和使用适当的索引来提高性能。

总之，SQL Server触发器是一种强大的数据库对象，可以在特定的数据库操作发生时自动执行相应的操作。通过合理使用触发器，可以实现数据完整性、数据一致性、数据审计等需求，提高数据库的可靠性和安全性。然而，在使用触发器时需要注意性能影响，并进行适当的优化和管理。