Oracle触发器转写成瀚高触发器（APP）

环境

系统平台：Microsoft Windows (64-bit) 10
版本：5.6.5

文档用途

本文章主要介绍oracle触发器转换成瀚高的触发器需要做哪些操作，主要修改哪些东西。

详细信息

Oracle触发器转换为HighGoDB触发器

1、创建触发器函数

将Oracle触发器的逻辑封装成触发器函数

2、创建触发器

创建触发器函数

转化为HighGoDB的触发器，步骤：

第一步，将Oracle触发器主逻辑封装成HighGoDB触发器函数

①、使用NEW替换：NEW，使用OLD 替换：OLD

②、函数返回类型为trigger最后return NEW 或者return NULL均可;

③、inserting updating deleting 替换成(TG_OP = ‘INSERT’) (TG_OP = ‘UPDATE’) (TG_OP = ‘DELETE’)

举例：

oracle触发器：

create or replace trigger "TRIG_ARCHIVE_HASTEN"

  before insert  or delete or update on td_archive_hasten

  for each row

declare

  isHasArchive number;

begin

  if inserting or updating then

    select count(*)

      into isHasArchive

      from td_status_temp

     where businessid = :new.businessid;



    if (isHasArchive > 0) then

      update td_status_temp

         set status = 1

       where businessid = :new.businessid;

    else

      insert into td_status_temp

        (businessid, status)

      values

        (:new.businessid, 1);

    end if;



  elsif deleting then

    select count(*)

      into isHasArchive

      from td_status_temp

     where businessid = :old.businessid;



    if (isHasArchive > 0) then

      update td_status_temp

         set status = 1

       where businessid = :old.businessid;

    else

      insert into td_status_temp

        (businessid, status)

      values

        (:old.businessid, 1);

    end if;

  end if;



end trig_archive_hasten;

转换成瀚高后的语法：

create or replace function TRIG_ARCHIVE_HASTEN()

returns trigger

as $$

declare

  isHasArchive number;

begin

  if(TG_OP = 'INSERT') or (TG_OP = 'UPDATE') then

    select count(*)

      into isHasArchive

      from td_status_temp

     where businessid = new.businessid;



    if (isHasArchive > 0) then

      update td_status_temp

         set status = 1

       where businessid = new.businessid;

    else

      insert into td_status_temp

        (businessid, status)

      values

        (new.businessid, 1);

    end if;



  elsif (TG_OP = 'DELETE') then

    select count(*)

      into isHasArchive

      from td_status_temp

     where businessid = old.businessid;

    if (isHasArchive > 0) then

      update td_status_temp

         set status = 1

       where businessid = old.businessid;

    else

      insert into td_status_temp

        (businessid, status)

      values

        (old.businessid, 1);

    end if;

  end if;

return new;

end;

$$ language plpgsql;

创建触发器

第二步，创建触发器

创建触发器时，不需要replace 然后 on表直接 for each row execute procedure 加触发器函数

create trigger TRIG_ARCHIVE_HASTEN

  before insert  or delete or update on td_archive_hasten

  for each row

  execute procedure TRIG_ARCHIVE_HASTEN();