基础 | 管理触发器

本文参考：
https://eco.dameng.com/docs/zh-cn/pm/manage-triggers.html

概要

DM是一个具有主动特征的数据库管理系统，其主动特征包括约束机制和触发器机制

• 约束机制
  主要用于对某些列进行有效性和完整性验证

• 触发器（TRIGGER）
  定义当某些与数据库有关的事件发生时，数据库应该采取的操作

触发器的介绍

与触发器相关的知识点：表、视图、DML语句、存储过程

触发器是一种特殊的存储过程，它在创建后就存储在数据库中（触发器满足触发条件时，由系统自动调用；存储过程需要手动调用）

触发器的特殊性在于它是建立在某个具体的表或视图之上的，或者是建立在各种事件前后的，而且是自动激发执行的，如果用户在这个表上执行了某个DML操作（INSERT、DELETE、UPDATE），触发器就被激发执行。

用户可以定义、删除和修改触发器

DM自动管理和运行触发器，从而体现系统的主动性

触发器是应用程序分割技术的一个基本组成部分，它将事务规则从应用程序的代码中移到数据库中，从而可确保加强这些事务规则并提高它们的性能。

触发器的对比

触发器与存储过程的区别

• 相同点：都是在服务器上保存并执行的一段DMSQL程序语句

• 不同点：存储过程必须被显式地调用执行，而触发器是在相关的事件发生时由服务器自动隐式地激发

触发器与激发触发器的语句之间的关系

• 触发器是激发它们的语句的一个组成部分，即直到一个语句激发的所有触发器执行完成之后该语句才结束，而其中任何一个触发器执行的失败都将导致该语句的失败，触发器所做的任何工作都属于激发该触发器的语句。

触发器的作用

触发器可发挥如下作用

• 扩展数据库功能，完成一些复杂工作
• 自动完成一些数据库的维护工作
• 提高业务规则的性能（由程序完成 ⇒ 由数据库完成，效率会更高）

触发器为用户提供了一种自己扩展数据库功能的方法。可以使用触发器来扩充引用完整性，实施附加的安全性或增强可用的审计选项。关于触发器应用的例子有：

• 利用触发器实现表约束机制（如：PRIMARY KEY、FOREIGN KEY、CHECK等）无法实现的复杂的引用完整性
• 利用触发器实现复杂的事务规则（如：想确保薪水增加量不超过25%）；
• 利用触发器维护复杂的缺省值（如：条件缺省）；
• 利用触发器实现复杂的审计功能；
• 利用触发器防止非法的操作。

触发器常用于自动完成一些数据库的维护工作。例如，触发器可以具有以下功能：

• 可以对表自动进行复杂的安全性、完整性检查
• 可以在对表进行DML操作之前或者之后进行其它处理
• 进行审计，可以对表上的操作进行跟踪
• 实现不同节点间数据库的同步更新

触发器分类

根据触发条件，DM有如下三类触发器

• 表级触发器：基于表中的数据进行触发
• 事件触发器：基于特定系统事件进行触发
• 时间触发器：基于时间而进行触发

触发器的使用

触发器是依附于某个具体的表或视图的特殊存储过程，它在某个DML操作的激发下自动执行

在创建触发器时应该仔细考虑它的相关信息。具体来说，应该考虑以下几个方面的问题：

• 触发器应该建立在哪个表/视图之上（被谁激发？）
• 触发器应该对什么样的DML操作进行响应（怎么激发？）
• 触发器在指定的DML操作之前激发还是在之后激发（什么时候激发？）
• 对每次DML响应一次，还是对受DML操作影响的每一行数据都响应一次（激发后要干嘛？）

在确定了触发器的实现细节后，现在就可以创建触发器了，创建触发器的语法格式为：

CREATE [OR REPLACE] TRIGGER 触发器名[WITH  ENCRYPTION]
	BEFORE|AFTER|INSTEAD OF
	DELETE|INSERT|UPDATE [OF 列名]
	ON 表名
	[FOR EACH ROW [WHEN 条件]]
	BEGIN
		DMSQL程序语句
	END;

详细语法介绍，可查看
https://eco.dameng.com/docs/zh-cn/pm/manage-triggers.html

创建触发器

创建一张"很重要的表"
drop table imp_tab;
create table imp_tab(cardid int, fund int);
insert into imp_tab values(1,1234),(2,2234),(3,3234);

创建一张"警告表"，当有人删除表imp_tab的记录时，记录删除的时间和警告信息
drop  table warn_tab;
create table warn_tab(warn_time date, message varchar(60));

创建触发器del_trg，在删除表imp_tab记录前，将当前时间和警告信息插入表warn_tab中
create or replace trigger del_trg
before delete
on imp_tab
begin
	insert into warn_tab values(sysdate,'Warning: Delete important table imp_tab');
end;

删除表imp_tab的记录，并commit（可试试不commit会怎样）
SQL> delete from imp_tab;
SQL> commit;
SQL> select * from warn_tab;

LINEID     WARN_TIME  MESSAGE                                
---------- ---------- ---------------------------------------
1          2021-11-22 Warning: Delete important table imp_tab

从触发器的执行情况可以看出，无论用户通过DELETE命令删除0行、1行或者多行数据，这个触发器只对每次DELETE操作激发一次，所以这是一个典型的语句级触发器

删除触发器

如果一个触发器不再使用，那么可以删除它

drop trigger if exists del_trg;

触发器失效 / 生效

启用或禁用触发器，命令如下

SQL> ALTER TRIGGER DEL_TRG DISABLE;
SQL> ALTER TRIGGER DEL_TRG ENABLE;

表级触发器

表级触发器都是基于表中数据的触发器，它通过针对相应表对象的插入/删除/修改等DML语句触发。（也就是说，当某张表的数据发生变化时，就激发触发器）

• 触发动作
  • INSERT、DELETE、UPDATE（update可指定列）
• 触发级别
  • 元组级（行级）
    对触发命令所影响的每一条记录都激发一次（FOR EACH ROW），DML语句影响了几行数据，就触发几次
    在元组级触发器中可以引用当前修改的记录在修改前后的值，修改前的值称为旧值，修改后的值称为新值。对于插入操作不存在旧值，而对于删除操作则不存在新值。
  • 语句级
    每个触发命令执行一次，DML语句执行了几次，就触发几次。
• 触发时机
  • BEFORE、AFTER、INSTEAD OF

事件触发器

触发条件基于特定系统事件的触发器

特定系统事件分为以下两类

• DDL事件（注意，不是DML！）
  包括CREATE、ALTER、DROP、GRANT、REVOKE以及TRUNCATE
• 系统事件
  包括LOGIN/LOGON、LOGOUT/LOGOFF、AUDIT、NOAUDIT、BACKUP DATABASE、RESTORE DATABASE、TIMER、STARTUP、SHUTDOWN以及SERERR（即执行错误事件）

事件触发器的级别

• 库级
• 模式级
  模式级触发器不能是LOGIN/LOGON、LOGOUT/LOGOFF、SERERR、BACKUP DATABASE、RESTORE DATABASE、STARTUP和SHUTDOWN事件触发器

触发时间

• 所有DDL事件触发器都可以设置BEFORE或AFTER的触发时机
• 系统事件中LOGOUT，SHUTDOWN仅能设置为BEFORE，而其它则只能设置为AFTER

与表级触发器不同，事件触发器不能影响对应触发事件的执行
其主要作用是帮助管理员监控系统运行发生的各类事件，进行一定程度的审计和监视工作

记录所有登录了数据库的用户名及登录时间

创建用户登录记录表login_tab
create table login_tab(name varchar(20),login_tiem datetime);

创建数据库级的事件触发器login_trg，若有用户登录数据库，则将用户名和登录时间插入表login_tab
create or replace trigger login_trg
after login
on database
begin
	insert into login_tab values(:eventinfo.loginname, :eventinfo.optime);
end;

创建test1用户，并使用该用户登录数据库
SQL> create user test1 identified by "Dm#123456";
SQL> conn test1/"Dm#123456" 
SQL> select user;

LINEID     USER()
---------- ------
1          TEST1

查看表login_tab（注意该表所属的schemas）
SQL> select * from login_tab;

LINEID     NAME   LOGIN_TIEM                
---------- ------ --------------------------
1          TEST1  2021-11-22 16:18:36.000000

时间触发器

时间触发器是一种特殊的事件触发器。时间触发器的特点是用户可以定义在任何时间点、时间区域、每隔多长时间等等的方式来激发触发器，而不是通过数据库中的某些操作包括DML、DDL操作等来激发，它的最小时间精度为分钟。

时间触发器与其它触发器的不同只是在触发事件上，在DMSQL语句块（BEGIN和END之间的语句）的定义是完全相同的。

时间触发器的创建语句如下：

CREATE [OR REPLACE] TRIGGER 触发器名 WITH ENCRYPTION
	AFTER TIMER ON DATABASE
	{时间定义语句}
	BEGIN
		执行语句
	END;

创建一个每分钟插入一行数据的时间触发器

创建测试表
create table insert_1min_tb(insert_time datetime);

创建时间触发器insert_timely_trg，每一分钟向表insert_1min_tb插入一行数据（当前时间）
create or replace trigger insert_timely_trg
after timer on database
for each 1 day for each 1 minute
begin 
	insert into insert_1min_tb values(sysdate);
end;
/

检查时间触发器的效果
SQL> select sysdate;

LINEID     SYSDATE            
---------- -------------------
1          2021-11-22 16:42:48

SQL> select * from insert_1min_tb;

LINEID     INSERT_TIME               
---------- --------------------------
1          2021-11-22 16:40:38.000000
2          2021-11-22 16:41:38.000000
3          2021-11-22 16:42:38.000000

时间触发器实用性很强，可定时做些操作

• 定期备份
• 定期更新表的统计信息
• 定时通知

疑问：
感觉时间触发器有点像"作业"（定时完成一些任务），两者有何差异么？
什么时候用时间触发器？什么时候用作业？两者可相互转换？

补充

在DM的数据守护环境下，备库上定义的触发器是不会被触发的

达梦云适配中心：
https://eco.dameng.com