cancel
Showing results for 
Search instead for 
Did you mean: 

CALLING SYSUUID in DDL

Former Member
0 Kudos

I am trying to create a table DDL in SAP HANA that populates UUID
when the user inserts records to the TEST table.


CREATE TABLE TEST
(
  TEST_ID        NUMBER(10)                     NOT NULL,
  TEST_NAME      NVARCHAR2(50)                  NOT NULL,
  UUID           NVARCHAR2(100)              SYSUUID  NOT NULL
);

But this statement fails and came to know calling SYSUUID in DDL is not supported. 

Then I tried to create a trigger as follows

CREATE TRIGGER TEST_TR
AFTER INSERT ON DIM
REFERENCING NEW ROW newRow
BEGIN
UPDATE TESTSET UUID = SYSUUID WHERE TEST_ID = :newRow.TEST_ID;
END;

now got the error message
SAP DBTech JDBC: [451]: modification of subject table in trigger not allowed: DEMO.TEST

Then I created a procedure to populate UUID as follows

create procedure Test(IN Testid Number) language SQLSCRIPT AS
BEGIN
UPDATE TESTSET UUID = SYSUUID WHERE TEST_ID = :Testid ;
END;

and calling this procedure in tigger

CREATE TRIGGER TEST_TR
AFTER INSERT ON DIM
REFERENCING NEW ROW newRow
BEGIN
CALL Test(:newRow.TEST_ID);
END;

Now get the following error

Could not execute 'CREATE TRIGGER TEST_TR AFTER INSERT ON DIM REFERENCING NEW ROW newRow BEGIN CALL ...' in 95 ms 671 µs .
SAP DBTech JDBC: [7]: feature not supported: call procedure is not supported in trigger

Please suggest a way to implement this SAP HANA. I dont like to implement this via application code.
I would like DB to populate UUID when the user inserts a record to the table.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

The summary of my findings are

1, Cannot call SYSUUID in DDL statement

2. Cannot create a trigger (after insert) that executes a update statement on the subject table.

3. Calling a procedure within a trigger is not allowed.

4. Cannot create a trigger on a table which has a trigger with update statement on the subject table.

( indirectly trying to do the same in step 2 above).

SAP HANA experts, is there any other way to resolve this issue on database layer without making changes to the application layer?

Does any one know if this feature will be available in SAP HANA SPS6?

Thanks

Former Member
0 Kudos

Hi Thayalini,

You are not willing to tweak your application code, but are you willing to tweak your insert statement call to HANA?

If yes you can populate SYSUUID just by tweaking the insert statement. Below is a sample table I created -

Capture.PNG

I call the following insert statements to insert to this table -

These are the values inserted into the table -

I am sure you must be calling an HANA specific insert statement within your java code, so just tweaking it to be similar to the above would be the simplest and easiest approach instead of trying for a complicated non supported trigger approach.

If you are not happy with this approach either, then hopefully SAP will plan to support the methods you are trying above, however from the reply by Rich Heilman in this thread that seems very unlikely.

Kind Regards,

Sharan

Former Member
0 Kudos

Hi Thayalini,

SAP DBTech JDBC: [451]: modification of subject table in trigger not allowed: DEMO.TEST

This error message seems to indicate that you are trying to insert/update/delete back into the table on which you created the trigger. This is not supported or possible.

Could not execute 'CREATE TRIGGER TEST_TR AFTER INSERT ON DIM REFERENCING NEW ROW newRow BEGIN CALL ...' in 95 ms 671 µs .
SAP DBTech JDBC: [7]: feature not supported: call procedure is not supported in trigger

Calling a procedure from a trigger is also not supported. You can find the documentation for this in sql reference guide on page 98 - 99.

In order to achieve your requirement you can try a 2 table approach. For example -

Create table A containing fields aa, bb, cc

Create table B containing fields aa, bb, cc, uuid

Create trigger trigger_name after INSERT on A referencing NEW ROW row_var

FOR EACH ROW

BEGIN

DECLARE uuidvar varchar(20) := ' ';

select sysuuid from dummy into uuidvar;

insert into B values( :row_var.aa, :row_var.bb, :row_var.cc, :uuidvar);

END;

So now when your user inserts aa, bb, cc into table A, the trigger is executed and transfers these values to table B and also inserts the SYSUUID generated by the select from dummy statement.

The disadvantage you have here is you have to maintain two tables with "almost" the same data for this to work. If all you want is a unique identifier then you can try using sequences, but if you want to be able to populate uuid the above solution is one possibility.

If you are inserting via an xsjs script, you can avoid triggers and have a single table. To achieve this within the xsjs script you can have a select on dummy to generate your uuid, then perform the insert of the user values along with the generated uuid at the same time.

Cheers,

Sharan

Former Member
0 Kudos

Hi Sharan,

Thanks for your reply. I won't be able to implement the suggested solution due to some other preconditions.

1. There are more than 100 tables. It is not good idea to a create duplicate tables.

2. I must use UUID not sequences due to the nature of the application

3. I am not using xsjs. It is a pure java implemetation and already tested fo Oracle/MS SQL Server

4. I cannot/dont want to  change application code , because I would like to use common code for Oracle,SQL Server and SAP HANA.

Based on the above, I would like to use trigger/procedure to populate UUID column with UUID.

Any other idea??

Thanks

Former Member
0 Kudos

Hi Thayalini,

Another method you can try is use two tables and have triggers on both, such that it works like this.

User inserts into table A.

Trigger1 on table A transfers value to table B and inserts into table B.

Table B will also have have a trigger2 on insert which will grab sysuuid and transfer values received from A and the sysuuid back to table A and perform an update operation on A.

Now you will also need a second trigger3 on table A which activates on update and this can drop all rows or just the specific row from table B.

I don't think you can create a trigger on a temporary table to perform the update back, and you cannot do the update on the table on which you created the trigger.

Triggers are capable of handling conditional logic, so maybe you can add the table name as a field in table B along with all fields of all tables and insert that via the trigger. Then the trigger on table B will use conditional logic, determine which table to update back and this way you can use a single table for all 100 tables. So essentially you will need 2 triggers on the table you want to insert to, and only 1 trigger should be enough for the write back.

The other method I can think of  is probably to insert via a procedure instead of using an insert statement. User calls procedure passing input parameters as all his/her inputs. Procedure grabs sysuuid from dummy, then inserts into target table the input values and the generated sysuuid. For this method you will need one procedure per table. Of course this only works if you can switch the insert statement for a call procedure statement, which as per your above comments seems to not be a viable option.

These may not be the best approaches but it could be possibility for your requirements.. Maybe if you wait a bit more some one else might come along with a better idea or you can try sapsupport

Cheers,

Sharan

Former Member
0 Kudos

Hi Sharan,

I tried your suggested method but still complains that subject table cannot be changed

User inserts into table A.

Trigger1 on table A transfers value to table B and inserts into table B.

Table B will also have have a trigger2 on insert which will grab sysuuid and transfer values received from A and the sysuuid back to table A and perform an update operation on A

This is not allowed. I tried little bit different way. I have create a trigger in TableA which inserts

a new record in TableB with TableA's unique id and its name 'TableA'. Again created another Trigger in

TableB which checks the table name and updates TableA in this case with uuid. but system complains that "Subject table cannot be changed"

Thanks

Former Member
0 Kudos

Hi Thayalini,

Try to create  "BEFORE" Insert trigger instead of  "AFTER" INSERT Trigger and check. 

Regards

Sayan

Former Member
0 Kudos

Hi Thayalini,

Looks like I misunderstood one thing. I was under the impression that a trigger could not modify the table on which the trigger was created on. However it seems a trigger cannot modify any table that has a trigger specified on it.

I tried a couple of other scenarios but with no luck. You can either use a intermediary table which the user inserts into, have a trigger on this which transfers values to your target table(table you actually want to see data in) along with the generated sysuuid. Since you want to avoid duplication of data, you can setup a cron job which executes a hdbsql script to drop all values on intermediary table on a timely(minutely/hourly/daily/weekly) basis.

The other option is to have a trigger on your target table, which transfers the only keys and sysuuid to another table. Then you can create a view to join these two tables to get your result.

I am out of further ideas and it doesn't seem likely that you can avoid changing some part of your java code. Maybe you can try opening a ticket with sapsupport but I am not sure how much help you will get.

Cheers,

Sharan