on 01-16-2015 7:31 AM
Hi all,
How can we transport HANA DB triggers which are not part of a delivery unit?
Thanks,
Christoph (new to HANA)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
85 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.