Skip to Content
-1

Create TRIGGER with TABLE STATEMENT on HANA

Hi,

I am not able to create a TRIGGER on a column table as soon as I try using a table. This workds fine for the same TRIGGER using rows. But I can not use rows as we have much too many records.

Here is what works fine:

-- Insert TRIGGER ROW
CREATE TRIGGER INSERT_TRIGGER_ROW
AFTER INSERT ON "test.track_delete::create_table.CCR_FINALMARKETID_01"
REFERENCING NEW ROW mynewrow
FOR EACH ROW
BEGIN
INSERT into "S00049863"."test.track_delete::create_table.CCR_FINALMARKETID_01_TRIGGER"
VALUES(:mynewrow."GUID", :mynewrow."CURVE", :mynewrow."DATE",
'', 'I',
CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP)
;
END;

Here is what does not work:

-- Insert TRIGGER TABLE
CREATE TRIGGER INSERT_TRIGGER_TABLE
AFTER INSERT ON "test.track_delete::create_table.CCR_FINALMARKETID_01"
REFERENCING NEW TABLE AS newtable
FOR EACH STATEMENT
BEGIN
INSERT into "S00049863"."test.track_delete::create_table.CCR_FINALMARKETID_01_TRIGGER"
SELECT "GUID", "CURVE", "DATE",
'', 'I',
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
FROM :newtable;
END;

The error message is not about the SQL statement but about the column table:

Could not execute 'CREATE TRIGGER INSERT_TRIGGER_TABLE AFTER INSERT ON ...'
SAP DBTech JDBC: [7]: feature not supported: Cannot create statement trigger on column table: test.track_delete::create_table.CCR_FINALMARKETID_01: line 2 col 18 (at pos 54)

Some help would be very welcome.

Regards,

Laurent

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 07 at 04:13 PM

    the url

    https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/1.0.12/en-US/20d5a65575191014946db96aaedbef5b.html

    says triggers are only valid on row tables - this is probably why your statement isnt working due to your column table

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 08 at 05:01 AM

    The help link above says 'Statement-level triggers are only supported in row-store tables.'. Your trigger has the statement 'FOR EACH STATEMENT' therefore you get this error. If you use 'FOR EACH ROW', it will work on a column table.

    Regards

    Neil

    Add comment
    10|10000 characters needed characters exceeded