Olap数据库触发器使用

举报
进击的白金之星 发表于 2020/11/16 23:50:29 2020/11/16
【摘要】 简要介绍GuassDB数据库触发器的使用方法与不同数据库之间的差异,旨在帮助GuassDB A 用户解决代码迁移时的遇到的改写问题。

OLAP数据库触发器使用总结

触发器类型:

 1.DML触发器: 创建在表上,由DMLinsertupdatedelete)事件引发

 2.instead of触发器: 创建在视图上并且只能在行级上触发,用于替代insertdelete等操作(由于oracleGuassDB中不能直接对有两个以上的表建立的视图进行DML操作,所以给出替代触发器,它是专门为进行视图操作的一种处理方法)

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

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

触发器组成:  

  1.触发事件:引发触发器被触发的事件 DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)DDL语句(如CREATEALTERDROP语句在数据库中创建、修改、删除模式对象)、数据库系统事件(如系统启动或退出、异常错误)、用户事件(如登录或退出数据库)。既可以是单个触发事件,也可以是多个触发事件的组合(只能使用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语法

image.png

示例:

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个行级触发器

 

image.png

 

特色参数:

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_shippingenable_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语句(SELECTINSERTUPDATEDELETE),不能使用DDL语句(CREATEALTERDROP)。

触发器中不能包含事务控制语句(COMMITROLLBACKSAVEPOINT)

触发器中不能使用commit语句,触发器的操作与触发事件(INSERT,UPDATE,DELETE)一起进行COMMITROLLBACK;

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

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

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

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

2 触发器中不能使用commit语句,触发器的操作与触发事件(INSERT,UPDATE,DELETE)一起进行COMMITROLLBACK;

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

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


【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。