目录
1、触发器的使用
语法格式:
CREATE [OR REPLACE] TRIGGER 触发器名[WITH ENCRYPTION]
BEFORE|AFTER|INSTEAD OF
DELETE|INSERT|UPDATE [OF 列名]
ON 表名
[FOR EACH ROW [WHEN 条件]]
BEGIN
DMSQL程序语句
END;
参数
1.< 触发器名 > 指明被创建的触发器的名称;
2.BEFORE 指明触发器在执行触发语句之前激发;
3.AFTER 指明触发器在执行触发语句之后激发;
4.INSTEAD OF 指明触发器执行时替换原始操作;
5.WITH ENCRYPTION 选项,指定是否对触发器定义进行加密;
6.FOR EACH 子句 指明触发器为元组级或语句级触发器。FOR EACH ROW 表示为元组级触发器,它受被触发命令影响、且 WHEN 子句的表达式计算为真的每条记录激发一次。FOR EACH STATEMENT 为语句级触发器,它对每个触发命令执行一次。FOR EACH 子句缺省则为语句级触发器;
7.WHEN 子句 表触发器中只允许为元组级触发器指定 WHEN 子句,它包含一个布尔表达式,当表达式的值为 TRUE 时,执行触发器;否则,跳过该触发器;
8.< 触发器体 > 触发器被触发时执行的 SQL 过程语句块。
BEFORE 触发器和 AFTER 触发器的举例:
BEFORE 触发器示例
SET SCHEMA OTHER;
CREATE OR REPLACE TRIGGER TRG_INS_BEFORE
BEFORE INSERT ON OTHER.READER
FOR EACH ROW
BEGIN
:NEW.READER_ID:=:NEW.READER_ID+1;
END;
SET SCHEMA SYSDBA;
该触发器在插入一条记录前,将记录中 READER_ID 列的值加 1。
CREATE TABLE T_TEMP(C1 INT,C2 CHAR(20));
新建表 T_TEMP。
SET SCHEMA OTHER;
CREATE OR REPLACE TRIGGER TRG_INS_AFTER
AFTER INSERT ON OTHER.READER
FOR EACH ROW
BEGIN
INSERT INTO SYSDBA.T_TEMP VALUES(:NEW.READER_ID, 'INSERT ON READER');
END;
SET SCHEMA SYSDBA;
该触发器在插入一条记录后,将插入的值以及操作类型记录到用于审计的表 T_TEMP 中。
SQL> create table test.tri_t1 (id int,name varchar(20));
操作已执行
已用时间: 6.905(毫秒). 执行号:915.
SQL> SET SCHEMA test;
操作已执行
已用时间: 0.420(毫秒). 执行号:0.
SQL> CREATE TABLE T_TEMP(C1 INT,C2 CHAR(20));
操作已执行
已用时间: 5.530(毫秒). 执行号:917.
SQL> CREATE OR REPLACE TRIGGER TRG_INS_AFTER
AFTER INSERT ON test.tri_t1
FOR EACH ROW
BEGIN
INSERT INTO test.T_TEMP VALUES(:NEW.id, 'INSERT ON READER');
END;2 3 4 5 6
7
8 /
操作已执行
已用时间: 16.292(毫秒). 执行号:918.
SQL> insert into test.tri_t1 values(1,'test');
影响行数 1
已用时间: 2.028(毫秒). 执行号:919.
SQL> commit;
操作已执行
已用时间: 1.600(毫秒). 执行号:920.
SQL> select * from test.t_temp;
行号 C1 C2
---------- ----------- --------------------
1 1 INSERT ON READER
已用时间: 1.064(毫秒). 执行号:921.
SQL>
例 2 INSTEAD OF 触发器举例
create table t1(a int,b int);
insert into t1 values(10,10);
insert into t1 values(11,11);
create view v1 as select * from t1;
在视图 v1 上创建 INSTEAD OF 触发器。
CREATE OR REPLACE TRIGGER tri1
INSTEAD OF UPDATE ON v1
BEGIN
insert into t1 values(111,111); //替换动作
END;
当执行 UPDATE 动作时候,就会触发。将下面的动作替换成触发器里的动作。
update v1 set a=100 where a=10;
查询结果如下:
A B
----------- -----------
10 10
11 11
111 111
由上面的查询结果可以看出。更新操作并没有成功,而是被触发器中的替换动作替换了。
需要注意的是,在 DM 的数据守护环境下,备库上定义的触发器是不会被触发的。
删除触发器
DROP TRIGGER [IF EXISTS] 触发器名;
使触发器失效/有效
ALTER TRIGGER 触发器 DISABLE;
ALTER TRIGGER 触发器 ENABLE;
重编译触发器
ALTER TRIGGER [<模式名>.]<触发器名> COMPILE [DEBUG];
2、表级触发器
激发表级触发器的触发动作是三种数据操作命令,即 INSERT、DELETE 和 UPDATE 操作。
根据触发器的级别可分为元组级 (也称行级)和语句级。
元组级触发器,对触发命令所影响的每一条记录都激发一次。假如一个 DELETE 命令从表中删除了 1000 行记录,那么这个表上的元组级 DELETE 触发器将被执行 1000 次。
语句级触发器,对每个触发命令执行一次。例如:对于一条将 500 行记录插入表 TABLE_1 中的 INSERT 语句,这个表上的语句级 INSERT 触发器只执行一次。
SET SCHEMA PERSON;
CREATE OR REPLACE TRIGGER TRG_DEL_ROW
BEFORE DELETE ON PERSON.PERSON
FOR EACH ROW //元组级:此子句一定不能省略
BEGIN
PRINT 'DELETE' || :OLD.NAME|| ' ON PERSON';
END;
CREATE OR REPLACE TRIGGER TRG_INS_ST
AFTER INSERT ON PERSON.PERSON
FOR EACH STATEMENT //语句级:此子句可省略
BEGIN
PRINT 'AFTER INSERT ON PERSON';
END;
SET SCHEMA SYSDBA;
3、事件触发器
可以触发的事件包含以下两类:
1.DDL 事件,包括 CREATE、ALTER、DROP、GRANT、REVOKE 以及 TRUNCATE;
2.系统事件,包括 LOGIN/LOGON、LOGOUT/LOGOFF、AUDIT、NOAUDIT、BACKUP DATABASE、RESTORE DATABASE、TIMER、STARTUP、SHUTDOWN 以及 SERERR(即执行错误事件)。
所有 DDL 事件触发器都可以设置 BEFORE 或 AFTER 的触发时机,但系统事件中 LOGOUT,SHUTDOWN 仅能设置为 BEFORE
事件触发器(系统触发事件)使用示例如下:
create or replace trigger test_trigger after LOGIN on database begin print'SUCCESS'; end;
//只要一登录,服务器就会打印出SUCCESS
4、时间触发器
时间触发器的特点是用户可以定义在任何时间点、时间区域、每隔多长时间等等的方式来激发触发器,它的最小时间精度为分钟。
创建语句如下:
CREATE [OR REPLACE] TRIGGER 触发器名 WITH ENCRYPTION
AFTER TIMER ON DATABASE
{时间定义语句}
BEGIN
执行语句
END;
例1:在t_temp每隔一分钟插入一行“HELLO WORLD”。
CREATE OR REPLACE TRIGGER timer1
AFTER TIMER on database
for each 1 day for each 1 minute
BEGIN
INSERT INTO test.T_TEMP VALUES(1, 'HELLO WORLD');commit;
END;
/
SQL> CREATE OR REPLACE TRIGGER timer1
AFTER TIMER on database
for each 1 day for each 1 minute
BEGIN
INSERT INTO test.T_TEMP VALUES(1, 'HELLO WORLD');
commit;
END;
/2 3 4 5 6 7 8
操作已执行
已用时间: 13.215(毫秒). 执行号:1204.
SQL>
SQL> select * from test.t_temp;
行号 C1 C2
---------- ----------- --------------------
1 1 INSERT ON READER
已用时间: 0.818(毫秒). 执行号:1205.
SQL> /
行号 C1 C2
---------- ----------- --------------------
1 1 INSERT ON READER
2 1 HELLO WORLD
已用时间: 0.271(毫秒). 执行号:1206.
例2:在每个月的第 28 天,从早上 9 点开始到晚上 18 点之间,每隔一分钟就插入一个字符串“Hello World”。
CREATE OR REPLACE TRIGGER timer2
AFTER TIMER ON DATABASE
FOR EACH 1 MONTH DAY 28
FROM TIME '09:00' TO TIME '18:00' FOR EACH 1 MINUTE
DECLARE
str VARCHAR;
BEGIN
INSERT INTO test.T_TEMP VALUES(2, 'HELLO WORLD');commit;
END;
/
达梦技术社区:https://eco.dameng.com/