HANA Procedure - Cyclic Dependency Error - disable trigger, perform update, enable trigger

[former_member229185]

Synopsis: A 'Cyclic dependency' error is encountered when calling a procedure that disables a trigger, performs an update on a table, enables a trigger.

Steps to reproduce:

select version
    from m_database;

VERSION
1.00.112.00.1457525470

-- 1) Create a table:
create table zh_trigger_test (
  val integer
);

-- 2) Create a trigger on that table:
create trigger trg_zh_trigger_test
  before insert or update on zh_trigger_test
  referencing NEW ROW as new
  for each row
  begin
    declare l_hit_count int;
    
    select count(*) into l_hit_count from dummy;      
  end;


-- 3) Create a procedure to insert a record into that table:
create procedure zh_cyclic_test
  language sqlscript as
  begin
    execute immediate 'alter table zh_trigger_test disable trigger';
    
    insert into zh_trigger_test
      select round(rand() * 100) from dummy;
      
    execute immediate 'alter table zh_trigger_test enable trigger';
  end;

-- 4) Call procedure:
call zh_cyclic_test;
Could not execute 'call zh_cyclic_test' in 6 ms 246 µs . 
[129]: transaction rolled back by an internal error: "ATLAS"."ZH_CYCLIC_TEST": line 4 col 5 (at pos 71): [129] (range 3): transaction rolled back by an internal error: Cyclic dependency found in a runtime procedure: Not allowed to call/modify runtime procedure ATLAS.ZH_CYCLIC_TEST during its execution 

-- 5) Cleanup:
drop table zh_trigger_test;
drop procedure zh_cyclic_test;

In this situation, the trigger needs to be disabled before performing a bulk update on the table because it basically crashes the database otherwise (separate issue). In a purely theoretical world, I can see how altering a table that a procedure depends on during the execution of that procedure could cause issues, however in this scenario we're simply disabling or enabling a trigger. Do we really need to drop and recreate the trigger, or possibly have a "trigger status" table that controls whether a trigger should act or not?

This would seem to be a common data warehousing operation, possibly more common with indexes.

[pfefferf · Accepted solution]

A workaround for the from my point of view misleading error is to execute the insert statement in your procedure as dynamic sql too.

  
  ...
  begin
    exec 'alter table zh_trigger_test disable trigger';
    
    exec 'insert into zh_trigger_test select round(rand() * 100) from dummy';
      
    exec 'alter table zh_trigger_test enable trigger';
  end;

Regards,
Florian

[former_member229185 · Accepted solution]

The master of hacks missed this one. Thanks Florian!