Olap数据库触发器使用

OLAP数据库触发器使用总结

触发器类型：

　1.DML触发器： 创建在表上，由DML（insert，update，delete）事件引发

　2.instead of触发器： 创建在视图上并且只能在行级上触发，用于替代insert，delete等操作（由于oracle、GuassDB中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，它是专门为进行视图操作的一种处理方法）

　3.DDL触发器： 触发事件时数据库对象的创建和修改

　4.数据库事件触发器：定义在数据库或者模式上，由数据库事件触发。 

触发器组成：  

　　1.触发事件：引发触发器被触发的事件 DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。既可以是单个触发事件，也可以是多个触发事件的组合（只能使用OR逻辑组合，不能使用AND逻辑组合）。

 当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。 

　　2.触发时间：即该触发器是在触发事件发生之前（BEFORE）还是之后(AFTER)触发。如果指定为BEFORE，则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则；如果指定为AFTER，则表示在执行DML操作之后触发，以便记录该操作或做某些事后处理。

　　3.触发操作：触发器触发后要完成的事情

　　4.触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，触发器才会执行触发操作。

　　5.触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发操作。

6.触发频率：语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。（比如delete多条数据时，行级触发器可能会执行多次，语句级触发器只会触发一次）

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值。

　　实现:  NEW 修饰符访问操作完成后列的值

     　　    OLD 修饰符访问操作完成前列的值 

特性	INSERT	UPDATE	DELETE
OLD	NULL	实际值	实际值
NEW	实际值	实际值	NULL

语法格式

GaussDB A 语法格式

 

CREATE [ CONSTRAINT ] TRIGGER trigger_name

{ BEFORE | AFTER | INSTEAD OF }

{ event [ OR ... ] }

ON table_name

[ FROM referenced_table_name ]

{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }

[ FOR [ EACH ] { ROW | STATEMENT } ]

[ WHEN ( condition ) ]

EXECUTE PROCEDURE function_name ( arguments );

 

示例：

CREATE TRIGGER insert_trigger

BEFORE INSERT ON test_trigger_table

FOR EACH ROW

EXECUTE PROCEDURE tri_insert_func();

Oracle语法格式

 

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;

 

示例：

CREATE OR REPLACE TRIGGER TRIGGER1

AFTER INSERT ON TRIGGERTEST    //插入后触发

FOR EACH ROW                   //行级触发器

BEGIN

   INSERT INTO tab1(ID,NAME) VALUES('22','33');

END;

 

Teradata语法

示例：

FOR EACH STATEMENT

CREATE TRIGGER UpdateForKey

AFTER UPDATE OF prime_key ON parent_tab

REFERENCING OLD TABLE AS OldTable NEW TABLE AS NewTable

FOR EACH STATEMENT (

UPDATE child_tab

SET for_key=NewTable.prime_key

WHERE child_tab.for_key=OldTable.prime_key;);

AFTER ROW

每当员工的加薪幅度大于10%时插入日志记录的触发器

CREATE TRIGGER RaiseTrig

AFTER UPDATE OF salary ON employee

REFERENCING OLD AS OldRow NEW AS NewRow

FOR EACH ROW

WHEN ((NewRow.salary-OldRow.salary)/OldRow.salary >.10)

INSERT INTO salary_log

VALUES ('USER', NewRow.name, OldRow.salary, NewRow.salary);

参数说明

通用参数说明

• BEFORE

触发器函数是在触发事件发生前执行。

• AFTER

触发器函数是在触发事件发生后执行。

• FOR      EACH ROW | FOR EACH STATEMENT

触发器的触发频率。

• FOR EACH       ROW是指该触发器是受触发事件影响的每一行触发一次。

o   FOR EACH STATEMENT是指该触发器是每个SQL语句只触发一次。

o   加上FOR EACH ROW 即为行级触发器，不加时为语句级触发器

·        condition

决定是否实际执行触发器函数的条件表达式。

当指定WHEN时，只有在条件返回true时才会调用该函数；WHEN表达式不能包含子查询。

• event

启动触发器的事件，取值范围包括：INSERT、UPDATE、DELETE。

通过OR同时指定多个触发事件。

• table_name

需要创建触发器的表名称。

视图不能对行直接操作，所以通常来说一张基表能建12个行级触发器

 

 

特色参数：

Olap数据库参数和使用的异同总结如下表一

                                                                                   表一 参数差异总结

功能项	GaussDB	Oracle	Teradata
触发频率	ROW|STATEMENT	ROW|STATEMENT	ROW|STATEMENT
触发时机	BEFORE | AFTER	BEFORE | AFTER	BEFORE | AFTER
触发场景	INSERT|UPDATE|DELETE|TRUNCATE	INSERT|UPDATE|DELETE	INSERT|UPDATE|DELETE|MERGE
触发范围	table|view|constraint	table|view|schema|tranction	table|view|order
参数赋值方式	column =new.culumn_name	column =:new.culumn_name	REFERENCING OLD AS OldRow NEW AS NewRow
when条件中参数调用方法	new.culumn_name	new.culumn_name	NewRow.culumn_name
不支持对象	列存表、临时表、unlogged表等类型表上创建触发器	临时表	• global temporary table   • hash index   • join index   • recursive view   • queue table   • trace table   • volatile table   • view
触发器大小	最大长度不超过63个字符。	触发器最大为32KB	最大值32767

 

详细差异如下

GaussDB 特色参数

GaussDB 支持约束触发器，且schema可以独立存在，所以主要参数差异在这两点。

·         

可选项，指定此参数将创建约束触发器，即触发器作为约束来使用。除了可以使用SET CONSTRAINTS调整触发器触发的时间之外，这与常规触发器相同。 约束触发器必须是AFTER ROW触发器。

·        trigger_name

触发器名称，该名称不能限定模式，因为触发器自动继承其所在表的模式，且同一个表的触发器不能重名。对于约束触发器，使用SET CONSTRAINTS修改触发器行为时也使用此名称。

·        AFTER

约束触发器只能指定为AFTER。

·        event

GuassDB 支撑TRUNCATE时间触发。

对于UPDATE事件类型，可以使用下面语法指定列：

UPDATE OF column_name1 [, column_name2 ... ]

表示只有这些列作为UPDATE语句的目标列时，才会启动触发器，但是INSTEAD OF UPDATE类型不支持指定列信息。

·        INSTEAD OF

触发器函数直接替代触发事件。

·        referenced_table_name

约束引用的另一个表的名称。 只能为约束触发器指定，常见于外键约束。由于当前不支持外键，因此不建议使用。

取值范围：数据库中已经存在的表名称。

·        DEFERRABLE | NOT DEFERRABLE

约束触发器的启动时机，仅作用于约束触发器。这两个关键字设置该约束是否可推迟。

详细介绍请参见CREATE TABLE。

·        INITIALLY IMMEDIATE | INITIALLY DEFERRED

如果约束是可推迟的，则这个子句声明检查约束的缺省时间，仅作用于约束触发器。

详细介绍请参见CREATE TABLE。

·        condition

在FOR EACH ROW触发器中，WHEN条件可以通过分别写入OLD.column_name或NEW.column_name来引用旧行或新行值的列。

当然，INSERT触发器不能引用OLD和DELETE触发器不能引用NEW。

INSTEAD OF触发器不支持WHEN条件。

对于约束触发器，WHEN条件的评估不会延迟，而是在执行更新操作后立即发生。 如果条件返回值不为true，则触发器不会排队等待延迟执行。

·        function_name

用户定义的函数，必须声明为不带参数并返回类型为触发器，在触发器触发时执行。

·        arguments

执行触发器时要提供给函数的可选的以逗号分隔的参数列表。参数是文字字符串常量，简单的名称和数字常量也可以写在这里，但它们都将被转换为字符串。

 

·        关于触发器种类：

o   INSTEAD OF的触发器必须标记为FOR EACH ROW，并且只能在视图上定义。

o   BEFORE和AFTER触发器作用在视图上时，只能标记为FOR EACH STATEMENT。

o   TRUNCATE类型触发器仅限FOR EACH STATEMENT。

Teradata特色参数

·        database_name/user_name

如果触发器在当前用户的默认值以外的数据库中创建，则为可选限定符

触发器名称，即可以指定database或者user。

正在创建或替换的触发器的名称。

·        trigger_name

在创建触发器的数据库中必须唯一。

·        ENABLED

启用触发器执行的关键字。ENABLED是默认值。

·        DISABLED

禁止触发器执行的关键字。

禁用的触发器的定义不会删除，但必须在启用之前启用它。

·        BEFORE

触发器在触发事件或触发语句执行之前执行。

BEFORE行触发器不能将数据更改语句作为触发操作SQL

语句。

BEFORE语句触发器在任何情况下都无效。

Teradata数据库在这两种情况下都返回错误消息。

• event

触发事件或触发语句可以是INSERT、DELETE、UPDATE或MERGE请求，

·        INSERT

该触发器的触发事件为以下之一：

• Atomic Upsert

• MERGE

·        UPDATE

此触发器的触发语句为以下之一：

• Atomic Upsert

• MERGE

 

·        column_name

列名集合中的列名。

更新列表中的任何列时，触发器将触发。

您可以根据需要将列表括在括号中。

如果不指定列名列表，则默认为所有列。

该列表不能指定行级安全列，也不能指定重复的列名。

对于INSERT或DELETE触发事件，column_name列表不适用。

·        table_name

必须是已存在的基表的名称。

table_name引用的对象不能为以下对象：

• global temporary table

• hash index

• join index

• recursive view

• queue table

• trace table

• volatile table

• view

·        ORDER

关键字，允许您控制请求中的触发器执行顺序

一个主题表上定义了多个触发器。

当两个或多个触发器具有

触发动作时间和触发事件相同。

如果触发器具有相同的ORDER值、触发器动作时间和触发器事件，则它们

按创建时间戳的顺序执行。

·        integer

赋值给ORDER的值。

注意事项

GaussDB注意事项

当前仅支持在普通行存表上创建触发器，不支持在列存表、临时表、unlogged表等类型表上创建触发器。

如果为同一事件定义了多个相同类型的触发器，则按触发器的名称字母顺序触发它们。

触发器常用于多表间数据关联同步场景，对SQL执行性能影响较大，不建议在大数据量同步及对性能要求高的场景中使用。

当触发器满足如下条件时，触发语句能和触发器一起下推到DN执行并提升触发器执行性能：

开关enable_trigger_shipping和enable_fast_query_shipping开启（默认均开启）。

源表触发器使用的触发器函数为plpgsql类型（推荐类型）。

源表与触发表分布键的类型、数量完全相同，均为行存表，且所属相同的nodegroup。

原INSERT/UPDATE/DELETE语句条件中包含所有分布键与NEW/OLD等值比较表达式。

原INSERT/UPDATE/DELETE语句在没有触发器的情况下原本就能query shipping。

源表上只有INSERT/UPDATE/DELETE AFTER/BEFORE FOR EACH ROW六类触发器，且所有触发器都可下推。

取值范围：符合标识符命名规范的字符串，且最大长度不超过63个字符。

Oracle注意事项：

一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。

触发器中不能使用commit语句，触发器的操作与触发事件（INSERT,UPDATE,DELETE）一起进行COMMIT和ROLLBACK;

因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。 

2） 触发器中不能使用commit语句，触发器的操作与触发事件（INSERT,UPDATE,DELETE）一起进行COMMIT和ROLLBACK;

REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。