Transport DB triggers

[christoph_hinssen]

Hi all,

How can we transport HANA DB triggers which are not part of a delivery unit?

Thanks,

Christoph (new to HANA)

[michael_gerstmayer · Accepted solution]

Hello,

Because a trigger is similar to index (it is a kind of property of table), it unfortunately can't be managed and exported/imported (at the moment) alone like as tables or views. But triggers definitions are directly exported together with the tables itself, see the example below:

CREATE SCHEMA TRIGGER_TEST;
SET SCHEMA TRIGGER_TEST;

CREATE TABLE TARGET ( A INT);
CREATE TABLE SAMPLE ( A INT);

CREATE TRIGGER TEST_TRIGGER
AFTER INSERT ON TARGET FOR EACH ROW
BEGIN
    DECLARE SAMPLE_COUNT INT;
    SELECT COUNT() INTO SAMPLE_COUNT FROM SAMPLE;
    IF :SAMPLE_COUNT = 0
    THEN
      INSERT INTO SAMPLE VALUES(5);
    ELSEIF :SAMPLE_COUNT = 1
    THEN
      INSERT INTO SAMPLE VALUES(6);
    END IF;
END;

EXPORT TARGET, SAMPLE AS CSV INTO '/tmp'

After exporting, the target folder /tmp/export/TRIGGER/TA/TARGET contains the file finalize.sql which holds the DDL for the trigger:

$$$
CREATE TRIGGER "TRIGGER"."TEST_TRIGGER" AFTER INSERT ON "TRIGGER"."TARGET" FOR EACH ROW

So if you export all the dictionary objects a schema and get all finalize.sql files, you can collect the definitions from them. Alternatively you can get the trigger definition by using the below SQL statement:

SELECT DEFINITION FROM TRIGGERS WHERE ...

Best regards,

Michael

[christoph_hinssen · Accepted solution]

Thanks Michael!

So if this export and import of the DDL is the only way, has someone more experience with the software logistics in such a case? E.g. wrapping this as text it into an entity which can be transported?

Best regards,
Christoph

[michael_gerstmayer · Accepted solution]

Hello Christoph,

I'm reaching out to the colleagues responsible in HANA development for clarification, as soon as I receveive a feedback I will get back to you.

Best regards,

Michael

[thomas_jung · Accepted solution]

There is no repository representation of the trigger yet. Therefore there is no way to transport a trigger definition as part of DU and have it created upon import.  Of course you could put the DDL commands in a SQL or SQLScript file within the repository and make that part of the DU. However someone has to run it manually after import.