ORACLE 触发器在GaussDB(DWS)上的实现

近期项目中遇到客户需要在GaussDB(DWS)上实现ORACLE的触发器。此博文记录一下实现过程。

DSC工具转换后，需要手工调整

一般SQL语句在ORACLE转换GaussDB(DWS)通过DSC工具转换后，能够直接执行。但触发器转换后，没办法直接执行。参考原来处理的代码，此处整理一个简单的ORACLE的Trigger，然后通过DSC转换后的代码

oracle源码

CREATE TABLE ORDERS (O_ORDERKEY       INT NOT NULL);
CREATE TABLE LINEITEM(L_ORDERKEY    INT NOT NULL, L_PARTKEY     INT NOT NULL);
--建触发器
CREATE OR REPLACE TRIGGER  TEST_TRIGGER
AFTER INSERT ON ORDERS
FOR EACH ROW
DECLARE
V_O_ORDERKEY INT;
begin
  V_O_ORDERKEY := :new.O_ORDERKEY;
  INSERT INTO LINEITEM VALUES(:new.O_ORDERKEY,1);
END test_trigger;
/

建好触发器后，可以测试一下功能，往ORDERS插入一条数据，看LINEITEM会否有数据插入

INSERT INTO ORDERS VALUES (1);
SELECT * FROM LINEITEM;

DSC转换后

CREATE
     TABLE
          ORDERS (
               O_ORDERKEY INT NOT NULL
          ) ;
CREATE
     TABLE
          LINEITEM (
               L_ORDERKEY INT NOT NULL
               ,L_PARTKEY INT NOT NULL
          ) ;
CREATE OR replace TRIGGER test_trigger AFTER INSERT
          ON ORDERS FOR EACH row DECLARE V_O_ORDERKEY INT ;
BEGIN
V_O_ORDERKEY := :new.O_ORDERKEY ;
INSERT
     INTO
          LINEITEM
          VALUES (
               :new.O_ORDERKEY
               ,1
          ) ;
END test_trigger ;

读者可以尝试拿着这个语句去试跑一下，转换后的语句是需要修改的。

DSC转换后修改点1

将FOR EACH ROW后面的存储过程内容放到一个函数中，例子如下：

create OR replace function test_trigger() returns trigger as $$
DECLARE V_O_ORDERKEY INT ;
BEGIN
V_O_ORDERKEY := :new.O_ORDERKEY ;
INSERT
     INTO
          LINEITEM
          VALUES (
               :new.O_ORDERKEY
               ,1
          ) ;
END ; 
$$ LANGUAGE plpgsql VOLATILE;

然后再建触发器，原文的OR replace去掉，不然会报错

CREATE  TRIGGER test_trigger AFTER INSERT
          ON ORDERS FOR EACH row 
execute procedure  test_trigger();

建好触发器后，可以测试一下功能，往ORDERS插入一条数据，看LINEITEM会否有数据插入

INSERT INTO ORDERS VALUES (1);
SELECT * FROM LINEITEM;

GaussDB(DWS)会报如下错误，此时就需要进行修改了

DSC转换后修改点2

报错的因为ORACLE的触发器中新一行的参数为:new ，而GaussDB(DWS)的新一行参数是不需要冒号:的。所以将函数中:new的:去掉

create OR replace function test_trigger() returns trigger as $$
DECLARE V_O_ORDERKEY INT ;
BEGIN
V_O_ORDERKEY := new.O_ORDERKEY ;
INSERT
     INTO
          LINEITEM
          VALUES (
               new.O_ORDERKEY
               ,1
          ) ;
END ; 
$$ LANGUAGE plpgsql VOLATILE;

往ORDERS插入一条数据，看LINEITEM会否有数据插入

INSERT INTO ORDERS VALUES (1);
SELECT * FROM LINEITEM;

发现还是会报错，说没有return值，此处在END前面增加一个RETURN NULL;

create OR replace function test_trigger() returns trigger as $$
DECLARE V_O_ORDERKEY INT ;
BEGIN
V_O_ORDERKEY := new.O_ORDERKEY ;
INSERT
     INTO
          LINEITEM
          VALUES (
               new.O_ORDERKEY
               ,1
          ) ;
RETURN NULL;
END ; 
$$ LANGUAGE plpgsql VOLATILE;

此时没有报错，触发器也成功实现了功能。

实现功能后的优化

触发器触发后，执行的一般都是单行条件的SQL。所以要注意建索引，让SQL走indexscan+nestloop的执行计划。
但之前遇到的一个情况，就是建了索引以后也存在扫描全表的情况，这样会使触发器执行的很慢。当时遇到的情况是5个表关联，多个表关联可能让执行计划误判，例如A.COL1=B.COL1 .... AND A.COL1=NEW.COL1 这样的关联条件，当时的情况就是B表扫描全表。此处可以等价地增加B表的过滤，例如A.COL1=B.COL1 .... AND A.COL1=NEW.COL1 AND B.COL1 = NEW.COL1 这样的话执行计划就知道走索引扫描了。或者使用hint的方式让B表直接走indexscan。

总结

ORACLE的触发器是能在GaussDB(DWS)上实现的，但是通过DSC转换工具后，需要进行一定的改写。

而触发器的性能不会特别慢，主要基于触发后执行的SQL执行的快慢。之前的经验是触发一个多个大表关联的merge into 语句，插入1000条记录花费的时间是20秒。