SQL触发器

目录

介绍

介绍

触发器分类

insert型触发器

创建触发器

实例操作

update型触发器

实例操作

delete型触发器

实例操作

查看和删除触发器

实例操作

• 介绍

• 介绍

• 触发器是与表有关的数据库对象
• 可以在insert，update，delete之前或之后触发并执行触发器中定义的SQL语句
• 这种特性可以协助应用系统在数据库端确保数据的完整性，日志记录，数据校验等操作
• 通过使用别名NEW和OLD来引用触发器中发生变化的内容记录

• 触发器分类

• insert型触发器
  • OLD:无(因为插入前无数据)
  • NEW:NEW表示将要或者已经新增的数据
• update型触发器
  • OLD:OLD表示修改之前的数据
  • NEW:NEW表示将要或者已经修改后的数据
• delete型触发器
  • OLD:OLD表示将要或者已经删除的数据
  • NEW:无(因为删除后状态无数据)

• insert型触发器

• 创建触发器

• 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型触发器，用于对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;

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  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型触发器，用于对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;

• 
• 复制代码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型触发器，用于对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,',name=',new.name,',money=',new.money,'}')); END$ delimiter ; -- 向account表添加一条记录 INSERT INTO account VALUES (NULL,'王五',2000); -- 查询account_log表 SELECT * FROM account_log;

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  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型触发器，用于对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,',name=',new.name,',money=',new.money,'}'));
  END$
  delimiter ;
  -- 向account表添加一条记录
  INSERT INTO account VALUES (NULL,'王五',2000);
  -- 查询account_log表
  SELECT * FROM account_log;

• update型触发器

• 创建触发器方式与insert一样

• 实例操作

• 
• 复制代码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) -- 操作参数 ); -- 创建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;

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  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) -- 操作参数
  );
  -- 创建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;

• 
• 复制代码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) -- 操作参数 ); -- 创建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_log表 SELECT * FROM account_log;

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  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) -- 操作参数
  );
  -- 创建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_log表
  SELECT * FROM account_log;

• delete型触发器

• 实例操作

• 
• 复制代码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) -- 操作参数 ); -- 创建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;

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  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) -- 操作参数
  );
  -- 创建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;

• 
• 复制代码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) -- 操作参数 ); -- 创建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_log表 SELECT * FROM account_log;

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  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) -- 操作参数
  );
  -- 创建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_log表
  SELECT * FROM account_log;

• 查看和删除触发器

• 查看触发器
• show triggers;
• 删除触发器
• drop trigger 触发器名称;

• 实例操作

• 
• 复制代码-- 查看触发器 SHOW TRIGGERS;

  1
  2
  -- 查看触发器
  SHOW TRIGGERS;

最后

以上就是坚定香菇最近收集整理的关于SQL触发器的全部内容，更多相关SQL触发器内容请搜索靠谱客的其他文章。