SQL Server触发器更新字段 sqlserver触发器更新当前表

目录

• 介绍

• SQL Server触发器
• 触发器与存储过程的区别
• 触发器的优点
• 注意事项

• 触发器的种类

• 1.DDL触发器（针对数据库级别）
• 2.DML触发器（数据操作）
• 3.登录触发器

• MySQL创建DML触发器

• insert型触发器
• update型触发器
• delete型触发器

介绍

触发器是一种特殊的存储过程它不能被显式地调用，而是insert，update，delete之前或之后被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束，这种特性可以协助应用系统在数据库端确保数据的完整性，日志记录，数据校验等操作，通过使用别名NEW和OLD来引用触发器中发生变化的内容记录。

SQL Server触发器

SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。这两个表由系统来维护，它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。 触发器执行完成后﹐与该触发器相关的这两个表也被删除。

Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。在delete或update语句执行时，这些相关行从trigger表移到了deleted表。
Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。在insert或update语句执行时，这些相关行从trigger表移到了deleted表。

触发器与存储过程的区别

主要区别在于运行方式，存储过程需要用户、应用程序或者触发器来显式地调用并执行，而触发器是当特定事件出现的时候，自动执行。触发器在数据库是独立的对象存储，与存储过程不同的是，存储过程通过其他程序来启动运行，而触发器是由一个事件来启动运行。即当某个事件发生时，触发器自动地隐式运行。并且，触发器不能接收参数。

触发器的优点

1.触发器自动执行，对表或对数据库的数据进行修改拿值等操作就会触发。

2.数据库中的相关表可以级联修改触发器。它可以通过数据库中的相关表进行层叠更改，这比把代码写在前台的做法更安全合理。

3.触发器可以实施限制，可以强制限制数据的完整性。这些限制比检查约束定义的限制更复杂。与CHECK约束不同，触发器可以引用其他表中的列。

注意事项

对相同的数据表、相同的事件只能创建一个触发器

比如对表 account创建了一个AFTER INSERT触发器，那么如果对表 account 再次创建一个 AFTER >INSERT触发器，SQL Server将会报错，此时，只可以在表 account上创建 AFTER INSERT 或者>INSTEAD OF UPDATE类型的触发器。灵活地运用触发器将为操作省去很多麻烦。

不再使用的触发器记得及时删除。

触发器的种类

1.DDL触发器（针对数据库级别）

当服务器或者数据库中发生数据的定义语言事件时就会调用DDL触发器，执行以下操作，可以使用DDL触发器。

防止对数据库架构的某些内容的更改
防止对数据库中的数据进行删除等操作。
或者记录数据库架构中的事件或修改操作。

2.DML触发器（数据操作）

DML触发器是当前数据库或者服务器中发生操作语言事件时就会执行的操作。DML触发器可以查询其他表的内容，还可以包含复杂的T-SQL语句。

触发器将触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误。则整个事务自动回滚。

分类:insert型触发器、update型触发器、delete型触发器

3.登录触发器

与SQL Server实例建立用户会话时将引发此事件。
注意：登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。

MySQL创建DML触发器

delimiter $
create trigger 触发器名称
before|after insert|update|delete
on 表名
for each row
begin
触发器要执行的功能;
end$
delimiter ;

---

-- 前置操作
CREATE TABLE account(
	id INT PRIMARY KEY auto_increment, -- 账户id
	name varchar(20), -- 姓名
	money DOUBLE -- 余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);
 
-- 创建日志表account_log
CREATE TABLE account_log (
			id INT PRIMARY KEY auto_increment, -- 日志id
			operation VARCHAR(20), -- 操作类型（insert，update，delete）
			operation_time datetime, -- 操作时间
			operation_id INT, -- 操作表的id
			operation_params VARCHAR(200) -- 操作参数
);

insert型触发器

-- 创建insert型触发器，用于对account表新增数据进行日志的记录
delimiter $
CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
		INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,'money=',new.money,'}'));
END$
delimiter ;
 
-- 向account表添加一条记录
INSERT INTO account VALUES (NULL,'王五',2000);
-- 查询account表
SELECT * FROM account;

update型触发器

-- 创建update型触发器，用于对account表修改数据进行日志的记录
delimiter $
CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
		INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('更新前{id=',old.id,',name=',old.name,',money=',old.money,'}','更新后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$
delimiter ;
 
-- 修改account表中李四的金额为2000
UPDATE account SET money=2000 WHERE id=2;
-- 查询account表
SELECT * FROM account;

delete型触发器

-- 创建delete型触发器，用于对account表删除数据进行日志的记录
delimiter $
CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
		INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$
delimiter ;
 
-- 删除account表中李四
DELETE FROM account WHERE id=2;
-- 查询account表
SELECT * FROM account;